Tom’s Tutorials For Excel: COUNTIF and SUMIF Anything or Nothing

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

Share Button
Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , , ,
2 comments on “Tom’s Tutorials For Excel: COUNTIF and SUMIF Anything or Nothing
  1. Allan Wilson says:

    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) ??

    • Tom Urtis says:

      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.

Leave a Reply

Your email address will not be published.

*