Countif <>

[VegasL]

Hello,

I am trying to count Non U.S.

The formula I have in cell c2, COUNTIF(A:A,"<>"&"United States") is giving wrong number. It should be 3.

Please see attached file.

Thanks

[benishiryo]

hi VegasL, your current COUNTIF is counting the blanks as non US too. so maybe:
=COUNTIFS(A:A,"<>"&"United States",A:A,"<>")-1

the minus 1 is for the header. or can you just put it in a smaller range? this is if you are using 2003 & below:
=SUMPRODUCT((A2:A100<>"United States")*(A2:A100<>""))

[VegasL]

Thanks Ben. It worked. Thats a very intersting use of sumproduct you came up with, 1* <> nothing = 1 *1.

I've often seen when sumproduct used with SUMPRODUCT(--(A2:A100<>"United States")*(A2:A100<>"")), what do those -- mean, and how does it affect the function?


Again thanks very much for you're expeditious help.