Count filtered cells with exclusion

[seroberts242]

Hi,

I want to count filtered cells but I don't want the count to include cells that contain "x" or empty cells.

I use this formula to count filtered cells:
=SUBTOTAL(103,A2:A140)

I use this to count with the exception of cells that contain "x":
=COUNTIFS(A2:A140,"<>",A2:A140,"<>x")

I guess I'm trying to figure out how to get all of this into one formula..

Thanks in advance!

 

[Jorge_B]

Hi,
You could try this.

=SUMPRODUCT((A2:A140<>"x")*(A2:A140<>""))

Hope it helps.

 

[seroberts242]

Thanks for your reply; unfortunately this isn't working for me. Not sure what's happening but the returned counts don't make sense.

For example I have filters on and applied, there are a total of 23 rows after filtering.

Column Q has 3 cells with text and the rest are blank. The count returned is 8.

Another column, O, has 8 cells with text and the rest are filled with "x". The count returned here is 21.

 

[XOR LX]

Hi.

=SUMPRODUCT(N(SUBTOTAL(3,OFFSET(A2,ROW(A2:A140)-MIN(ROW(A2:A140)),))),N(A2:A140<>""),N(A2:A140<>"x"))

Regards

 

[seroberts242]

Perfect, thank you XOR LX!

 

[XOR LX]

Sure! You're welcome!