**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.

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.