Is there a way to =COUNTBLANK for cells pulled with =FILTER?-CodePudding

I am attempting to count the number of blanks in a large (1 million cells) dataset, that has been pulled through using a =FILTER formula (from a larger dataset).

Due to the way Excel handles the =FILTER formula, every single cell is populated with =FILTER, even if it's only pulling a blank value.

Is there any way to count the blank values that are in the =FILTERed dataset? I've tried using =COUNTBLANK and =COUNTIF('RANGE:RANGE'=""), but because the cells aren't physically empty, they just have no value, it always yields zero.

The thing is, the same formula works fine in Google Sheets, as Google Sheets doesn't appear to put =FILTER into every cell of the filtered array. I've included an example here showing a dummy example of my dataset, including a side-by-side screenshot of how it behaves in Google Sheets Vs Excel: https://docs.google.com/spreadsheets/d/1-_mULvQG580EqSMci9gY3Ccll3yjwILTwI6TxglUQgs/edit#gid=0

Any help would be appreciated. Thanks!

CodePudding user response：

The only way I know how to do it is by counting the number of rows and subtracting the cells that contain "PENDING".

=ROWS(J4:J7)-COUNTIF(J4:J7,"PENDING")

CodePudding user response：

In your representative data in Excel, the cells aren't blank but have a value of 0 so you can just count that:

=COUNTIF(J4:J7,"0")

If you want a count of "true" blank cells (although COUNTBLANK should work for this) you can check the length of text in the cells using:

=SUMPRODUCT(--(LEN(J4:J7)=0))

=ROWS(J4:J7)-SUMPRODUCT(--(LEN(J4:J7)>0))