Suppose you have a string "abc123def45cd" and you want to count non numbers in this. If your string is in A1, use following formula in A1 =IF(LEN(TRIM(A1))=0,0,SUMPRODUCT(–NOT(ISNUMBER((–MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))))))
Tips & Tricks 18 – Count No. of Unique Values
Use following formula to count no. of unique values – =SUMPRODUCT((A1:A100<>"")/COUNTIF(A1:A100,A1:A100&"")) Credit – There are various ways to accomplish above. I knew all other formulas. But the above formula is the sleekest. I learnt it from a posting to Excel Microsoft Community.
Tips & Tricks 17 – Count No. of Cells which are containing only Characters
Hence, if your cell is having a number 2.23, it will not be counted as it is a number. Use below formula considering your range is A1:A10 =COUNTIF(A1:A10,"*")
Tips & Tricks 16 – Count No. of Cells Having Numbers Only
COUNT function counts only those cells which are having numbers. Assuming your range is A1:A10, use following formula =COUNT(A1:A10)
Tips & Tricks 15 – Most Frequently Occurring Value in a Range
Assuming, your range is A1:A10, enter the below formula as Array Formula i.e. not by pressing ENTER after entering your formula but by pressing CTRL+SHIFT+ENTER. This will put { } brackets around the formula which you can see in Formula Bar. If you edit again, you will have to do CTRL+SHIFT+ENTER again. Don't put {…
Tips & Tricks 14 – Count Cells Starting (or Ending) with a particular String
1. Say you want to count all cells starting with C =COUNTIF(A1:A10,"c*") c* is case insensitive. Hence, it will count cells starting with both c or C. Suppose you want to find all cells starting with Excel. =COUNTIF(A1:A10,"excel*") 2. For ending =COUNTIF(A1:A10,"*c") c* is case insensitive. Hence, it will count cells starting with both c…
Tips & Tricks 13 – Copy and Paste All Defined Names in the Sheet
You are wondering that if you can copy and paste all defined names in the sheet. Do following 1. Formulas Tab > Use in Formula > Paste Names 2. Click on Paste List to paste all defined names.
Tips & Tricks 12 – Converting Date to a Quarter
Assuming date is in Cell A1. You want to convert it into a quarter (1, 2, 3 & 4). Jan to Mar is 1, Apr to Jun is 2, Jul to Sep is 3 and Oct to Dec is 4. =CEILING(MONTH(A1)/3,1) OR = ROUNDUP(MONTH(A1)/3,0)
Tips & Tricks 11 – Convert a Number to Named Month
I am deleting this post as I have written a proper article on this and below is a link to this https://excelbianalytics.com/wp/article-24-convert-a-number-to-a-month-name/
Tips & Tricks 10 – Convert from Word Tables to Excel
The best way to do this would be through utilities. The best one which I found out and is free is following – http://watermark-images.com/convert-word-to-excel.aspx Another way is to copy Word tables one by one (or in one go if layout permits) into Excel – http://office.microsoft.com/en-in/excel-help/copy-a-word-table-into-excel-HP010254130.aspx