MrExcel Publishing
Your One Stop for Excel Tips & Solutions

countif


Posted by Alexia on December 05, 2001 1:52 PM

How do u not include a blank cell in a countif


Posted by Aladin Akyurek on December 05, 2001 1:54 PM

How do you mean?

Aladin

Posted by alexia on December 05, 2001 1:57 PM

I want to count only the cells with value either negative or positive. Currently, it counts all cells even the ones without data

Posted by Aladin Akyurek on December 05, 2001 2:08 PM

Try:

=SUMPRODUCT((ISNUMBER(SEARCH("?*",A1:A4)))+0)

Aladin

Posted by Aladin Akyurek on December 05, 2001 2:12 PM

=COUNTIF(A1:A4,">0")+COUNTIF(A1:A4,"<0")

will also work.

Aladin


Posted by Mark W. on December 05, 2001 2:22 PM

Try this!

=COUNTIF(A1:A5,"=") counts the blank cells
=COUNTIF(A1:A5,"<>") counts the non-blank cells

Posted by Mark W. on December 05, 2001 2:30 PM

Here's some more...

=COUNTIF(A1:A5,"=*") counts text values
=COUNTIF(A1:A5,"<>*") counts numeric and blank values

Posted by IML on December 05, 2001 2:48 PM

Re: Here's some more...

I don't get the first two, care to explain?
=COUNTIF(A1:A5,"=") counts the blank cells
=COUNTIF(A1:A5,"<>") counts the non-blank cells

for example
=COUNTIF(A1:A5,"JA")
would return 0 if Jane existed?
I was thinking it was defauting to zero, but that doesn't make any sense because the first one returns 0 if a1:a5 are populate with zeros.
I'm not questioning if they work, just why.
Thanks,
Ian

Posted by Mark W. on December 05, 2001 3:12 PM

Re: Here's some more...

ERR

Posted by IML on December 05, 2001 3:20 PM

Thanks - I don't not get it now! (nt)