Numbers COUNTIF (and friends) seems incapable of matching NULL strings. Is that correct?

Question

Level 4

2,677 points

Numbers COUNTIF (and friends) seems incapable of matching NULL strings. Is that correct?

(Note: This is for both Numbers for the Mac, and Numbers for iPad, etc.)

I have been using Spreadsheets, from various sources, for decades.

It's always been just a bit "difficult" to create computed Cells (Cell Formulas [Formulae]) that Look like Blank Cells, but will work appropriately with further computations.

However, this time, I've run into a particularly "nasty" case with Numbers, which wouldn't be the case in Excel (even in the days before Array Formulas [Formulae]): COUNTIF (and friends) seem incapable of matching Cells with NULL string ("") values (whether explicit or from Formulas [Formulae]).

In Excel, Google Sheets, and both OpenOffice/LibreOffice Calc, COUNTIF (and friends) will match ONLY Blank (ISBLANK is true) Cells, if one matches against "=" ("="&"", "="&BLANKCELL [some Blank Cell], etc.), but will match both Blank Cells AND Cells having the NULL string as a Value (="", IF(true,""), IFERROR(0/0,""), etc., or an explicitly copied and Value pasted result of such), if one matches WITHOUT the "=".

I know. The documented, and usual behavior, is to treat cases without the "=" prepended as equivalent to the "=" prepended cases.

However. This is a case where a small deviation from that "rule" provides significant benefits, that all other Spreadsheet programs I have current access to agree upon!

Besides: Isn't it rather "embarrassing" (to say the least) to NOT be able to have the NULL string ("") match the NULL string?

Posted on Jul 6, 2020 2:41 PM

Reply

Answer 1

Top-ranking reply

Barry

Level 9

60,023 points

Jul 7, 2020 12:22 AM in response to Halliday

I've generally used an auxiliary column to detect and flag the cells containing either 'nothing' (blank) or a null string, then used COUNIF on that column.

Example:

labels in column A describe the content of the cell on that row of column B.

C n the Aux column. D2 contains COUNTIF(C,"<1")

Column C can be hidden

Formula in the Aux column (C) is LEN(B2) filled down to the end of the column.

Regards,

Barry

Reply

Answer 2

Badunit

Level 7

30,336 points

Jul 8, 2020 11:23 AM in response to Halliday

There is an inconsistency between Numbers and Excel but I'm not so sure Excel has the right answer either.

I don't find it any more "embarrassing" that Numbers doesn't count null strings than I do Excel includes blanks when you specifically requested it to count only nulls. Both are wrong answers.

Numbers and Excel agree in these cases:

=COUNTIF(A1:A5,"=") counts the blank cells but not null strings. Excel gives an annoying warning, though.

=COUNTIF(A1:A5,"="&"") counts the blank cells but not null strings. Excel gives an annoying warning, though.

=COUNTIF(A1:A5,C1), where C1 is blank, apparently counts nothing (no blanks, no nulls, nothing). I'm not sure what the "correct" answer should be but they both do the same thing.

but not for these formulas

=COUNTIF(A1:A5,""), Excel counts blanks and nulls (and gives an annoying warning) while Numbers counts only blanks, no nulls. At least Numbers is internally consistent, given that "" and "=" should be treated as the same thing.

=COUNTIF(A1:A5,C1), where C1 has the formula ="", Excel counts blanks and nulls (and gives a warning) while Numbers will count only blanks, no nulls.

A possible solution to your problem would be to use " " instead of "" in your formulas so that you are not trying to count null strings.

=COUNTIF(A1:A5,"") + COUNTIF(A1:A5," ")

I may put in a Numbers feedback report about the inconsistency with Excel, though, and the inability to count null cells (*or at least our inability to figure out how).

Reply

Answer 3

Barry

Level 9

60,023 points

Jul 7, 2020 5:17 PM in response to Halliday

"Is Numbers supposed to be a Real Spreadsheet Program, or just a Toy, of a spreadsheet program?"

Rhetorical question, I'm afraid. Anyone asking it already knows their answer.

If it won't do the job you need it to do, then Numbers isn't the right tool for that job. If it can do the job, but requires more effort from the user than the user willing to apply, then Numbers may not be the right tool for that user.

Or may not yet be the tool for that user.

In this user to user community, we can offer suggestions on how to do things with the application as it currently exists, but we're not part of the development team, and can't make changes in the application itself.

If that is what's needed to solve your issue, you'll need to Provide Numbers Feedback, using that menu item in the Numbers menu, and either report this as a bug, or make a Feature enhancement request to change the behaviour of the …IF and …IFS functions to more closely match your need.

Some 'light reading' before making the request: Unsolicited Idea Submission Policy

Regards,

Barry

Reply

Answer 4

Halliday Author

Level 4

2,677 points

Jul 7, 2020 9:37 AM in response to Barry

Yes, @Barry. We can always create auxiliary Columns (or Rows) to work around a number of "needly" issues. In fact, we could use auxiliary Columns/Rows to replace the ...IFS Functions all together! (Replacing them with the ...IF forms, only.)

Is Numbers supposed to be a Real Spreadsheet Program, or just a Toy, of a spreadsheet program?

Even Google Sheets (which was a Toy of a spreadsheet program for many years) gets this correct. 😞

Reply

Answer 5

Badunit

Level 7

30,336 points

Jul 8, 2020 7:46 PM in response to Halliday

Halliday wrote:

By the way, Badunit, as for you example "=COUNTIF(A1:A5,C1), where C1 is blank":

In both Numbers and Excel, the default value of a Blank cell is the number zero (0). So, in both cases, such will match zeros (0)!

Very true. Wasn't thinking I guess. This could be considered another inconsistency in the software. I asked it to find matches to what is in cell C1, cell C1 is a blank cell, it should be matching blank cells not zeros. If I wanted to find zeros, I would have put a zero in cell C1.

Feedback has been submitted.

Reply

Answer 6

Halliday Author

Level 4

2,677 points

Jul 8, 2020 10:06 AM in response to Barry

Thanks, @Barry.

As I should have responded, initially, I don't need recommendations on how to work around the ...IF and ...IFS functions NOT being capable of matching NULL strings. I have myriad such workarounds (simpler, from my perspective, than an auxiliary column that produces something the ...IF and ...IFS functions CAN match against, and easier to "back out of", if I move to other software, or Numbers "gets its act together".)

What I needed to find out is whether I am correct that the ...IF and ...IFS functions ARE INCAPABLE of matching NULL strings, or, if I am wrong, and there IS some way to get those functions to directly recognize such.

As for "toy" programs, I actually PAID for Numbers on the Mac, before it became a free application.

I far prefer its user interface to ALL other spreadsheet-like programs (with the exception of Lotus IMPROV, on the NeXT!).

Besides. If Numbers IS a "toy" program, it would be great if Apple would release the source code, so we can more fully "play" with it! (I would LOVE to be able to reproduce IMPROV!)

Reply

Answer 7

Halliday Author

Level 4

2,677 points

Jul 8, 2020 10:26 AM in response to Barry

Thanks, @Barry.

As I should have responded, initially, I don't need recommendations on how to work around the ...IF and ...IFS functions not being capable of matching NULL strings. I have myriad such workarounds (simpler, from my perspective, than an auxiliary column that produces something the ...IF and ...IFS functions can match against, and easier to "back out of", if I move to other software, or Numbers "gets its act together".)

What I needed to find out is whether I am correct that the ...IF and ...IFS functions are incapable of matching NULL strings, or, if I am wrong, and there is some way to get those functions to directly recognize such.

As for "toy" programs, I actually paid for Numbers on the Mac, before it became a free application.

I far prefer its user interface to all other spreadsheet-like programs (with the exception of Lotus IMPROV, on the NeXT!).

Besides. If Numbers is a "toy" program, it would be great if Apple would release the source code, so we can more fully "play" with it! (I would LOVE❤️ to be able to reproduce IMPROV [and beyond]!)

Reply

Answer 8

Halliday Author

Level 4

2,677 points

Jul 8, 2020 6:12 PM in response to Badunit

By the way, Badunit, as for you example "=COUNTIF(A1:A5,C1), where C1 is blank":

In both Numbers and Excel, the default value of a Blank cell is the number zero (0). So, in both cases, such will match zeros (0)!

When in a string context, in both Numbers and Excel, a Blank cell is the NULL string: so "=", "="&"", and "="& C1 ("where C1 is blank") are all identical to "=".

These are simply the limitations of the system we are working within.

Additionally, it is not a "given" that " "" and "=" should be treated as the same thing." It is simply an assumption, based upon the equality when involving non-NULL string and other values.

In any case, I would greatly appreciate you "put[ting] in a Numbers feedback report about the inconsistency with Excel, though, and the inability to count null cells (*or at least our inability to figure out how)."

(Incidentally, the version of Excel I tested on did not give me any warnings, that I saw.)

Reply

Numbers COUNTIF (and friends) seems incapable of matching NULL strings. Is that correct?

Similar questions