Counting empty cells

[Cunner]

Ok folks, this seems to be a trivial thing (and it probably is), but I encountered the following issue:

In a countifs formula I have one criterium that shall tell excel to count only cells that are not empty. I used this formula:

Please Login or Register  to view this content.

Let's say it does something, but it doesn't give me the "right" result (I checked by setting filters).

While investigating this I tried the following to formulas:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

To my understanding these to formulas should have mutually exclusive results.
In fact formula one returns 200.932, formula 2 returns 48.908. (As you can see from the range, there is a total of 200.932 cells).

Can you explain, what exactly "<>" does? In this example it counts cells as non-empty, even though there are definitely empty cells.
I have an example sheet where I used the countifs formula from above and it works...

[BB1972]

Cunner, you need to put the = sign OUTSIDE the "".

EQUALS BLANK: =""
EQUALS NOT BLANK: <>""

Hope this helps.



EDIT: Put the equals or the not equals sign (<>) outside (before) the "".

EDIT # 2: I've just noticed you're using countif, not if - I apologise for any confusion I've caused, and am going for a coffee to help kick-start my head before I get anything else wrong. Sorry, Cunner.

[Portuga]

In excel blanks that are derived from formulas, an if statement for example (;"") are not considered true blanks since there is a formula in the cell.
The cell shows nothing, but there is something there.

The countblank formula though, was designed to include the derived blanks as blanks, the countif wont.

You can combine both your formulas to get what you want

Please Login or Register  to view this content.

[Cunner]

Thank you Portuga.
Point is, that there isn't even a formula in the respective empty cells...

I will try to adapt your solution, though.

[Portuga]

As a test try the following: (this will also give you the results)

Create a pivot table . Range I1:I200934 (I1 as the field title)

Put the field in both the total section and the row section of the pivot.

The pivot will make the difference between true blanks and derived blanks. You can use this method to see exactly what the range has in terms of blanks.

If you then tick out the blanks, the pivot will give the total of non blanks.