Share via

Count blanks when filtered

Anonymous
2018-02-08T13:17:00+00:00

Good morning,

In Excel, I am using this formula (and it works) to count filtered records containing "24" in the Z column, in a spreadsheet:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(Z:Z,ROW(Z:Z)-MIN(ROW(Z:Z)),,1)),ISNUMBER(SEARCH("24",Z:Z))+0)

How can I alter the formula to count blanks (no value) in the Z column? Double quotes don't seem to work.

Thanks

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2018-02-13T04:50:35+00:00

    Have a look at the following link.

    The formula (with example) given there should be able to help you resolve your problem with slight modification.

    https://www.extendoffice.com/documents/excel/4874-excel-count-blank-cells-in-filtered-range.html

    Hope this Helps.

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments
  2. Anonymous
    2018-02-13T06:43:01+00:00

    Create a helper column, say AA2:AA20. Enter some data (say 1) in each of the cells in AA2:AA20. Then use below formula:

    =SUBTOTAL(3,AA2:AA20)-SUBTOTAL(3,Z2:Z20)

    This will return the count of blank cells in the range Z2:Z20.

    Regards,

    Amit Tandon

    www.globaliconnect.com

    Was this answer helpful?

    0 comments