Tom’s Tutorials For Excel: COUNTIF and SUMIF Anything or Nothing
You can use the “<>” and “=” operators with worksheet functions for identifying the presence or absence of values in a cell.
In Picture #1, the formula in cell E4 is =COUNTIF(A2:A10,"<>").
The number 6 is returned because 6 items appear in range A2:A10.
Picture #1
In Picture #2, the formula in cell E5 is =COUNTIF(A2:A10,"=").
The number 3 is returned because cells A4, A6, and A8 are blank in range A2:A10.
Picture #2
In Picture #3, the formula in cell E8 is =SUMIF(A1:A10,"<>",B1:B10).
The dollar figure $51,249 is returned as the sum of cells B2, B3, B5, B7, B9, and B10, corresponding to the presence of values in cells A2, A3, A5, A7, A9, and A10.
Picture #3
In Picture #4, the formula in cell E9 is =SUMIF(A1:A10,"=",B1:B10).
The dollar figure 14,825 is returned as the sum of cells B4, B6, and B8 because no values are present in cells A4, A6, and A8.
Picture #4
I HAVE A CELL CONTAINING A TEXT-STRING. HOW CAN I REFERENCE THIS CELL SO THAT I CAN COUNT HOW MANY CELLS IN A RANGE CONTAIN THE SAME TEXT STRING AS THE REFERENCED CELL eg:- COUNTIF(B2:N100,A2) ??
If tom is in cell A2
=COUNTIF(B2:N100,”=”&A2)
counts how many cells contain only tom.
=COUNTIF(B2:N100,”*”&A2&”*”)
counts how many cells contain tom such as tomato or atom or tom.