200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
I need a formula with COUNTIF that shows cells thta not contain specific text as "Nil" instead of '0' (zero) . Eg: In a range of cells the specific text '50' if not contain then the result presently am getting as 0. I just want result as 'Nil' instead of 0
=COUNTIF(C4:C14,50)
The formula below should do the job.
=IF(COUNTIF(C4:C14, 50)=0,"Nil","")Your question appears to indicate confusion as to the nature of cell contents. Excel differentiates clearly between numbers and text and has an equally clear preference for numbers. That is because Excel is designed for processing numbers. If you want to process text, use Word.
Any number you enter in a cell is just a number. Enter 50 in a cell and that number can be multiplied, divided, summed or otherwise manipulated in whichever way numbers can be manipulated. If you want the 50 to be text, you can either format the cell as Text or you can enclose it in double quotation marks, like ="50". A third way is to enter '50 (preceding the number with a single quotation mark which tells Excel to format the cell as Text and interpret its content accordingly.
Now, COUNTIF(C4:C14, 50) is totally different from COUNTIF(C4:C14, "50") and 0, the number, is different from "0", the text. For this reason you could achieve the result you want also with your own, original formula, =COUNTIF(C4:C14, 50) if you apply a custom format to the cell as shown below. This is because the COUNTIF function returns a number, possibly 0, not "0".
0;0;"Nil"Format (or right-click) -> Format Cells -> Number tab -> Custom.
Then enter the above formula in the Type field. The effect of this format is that when the cells' value is zero (whether by entry or formula result) it will be displayed as "Nil". The rule applies only to the cells with this format.