Subtotal countif?

Hi Guys,

I know there must be a way to do this, I have a filtered list and use the following formulae to count cells which meet a criteria:

Please Login or Register  to view this content.

it basically states: ALL CELLS - BLANK CELLS - "N/A" CELLS - "(dd/mm/yy)" CELLS - "(mmm-yy)" CELLS

I would like this count to reflect the data that has been filtered, at the moment it counts the total regardless of what has been filtered.

I know you can do this through SUBTOTAL but i have no idea how to get it to recognise text (dates in brackets)

Any help would be appreciated.

F

I think you might be thinking of SUBTOTAL rather than SUMPRODUCT.

SUBTOTAL can exclude filtered and/or hidden rows. Not sure how you'd do that with SUMPRODUCT (Edit: not SUBTOTAL)

You might be able to produce the count(s) you want with a Helper column. Create a formula that sets a 1 if the row meets all those conditions, and 0 if not.

Then subtotal the Helper column. The subtotal doesn't have to be in the same column as the Helper "flags" so you could hide the Helper column ... and no one need know.

Regards, TMS

Untested..

=SUMPRODUCT(SUBTOTAL(3,OFFSET(B15:B143,ROW(B15:B143)-ROW(B15),,1)),--(F15:F143<>"("&"*"&"/"&"*"&")"),--(F15:F143<>"("&"*"&"-"&"*"&")"))

Thanks for the rep. Not sure how much I helped; Ace_XL came up with the solution.

But glad you got an answer to what looked a difficult question.

Regards, TMS

i came up with this monstrosity:

Please Login or Register  to view this content.

where list_f = $F$15:$F$143 and list_b = $B$15:$B$143.

when in unfiltered state, this formula's result is the same as:

Please Login or Register  to view this content.

also, your data actually range all the way up to row 332, while the range in your formulae are limited to row 143. i hope you have done this knowingly.

...<>"("&"*"&"/"&"*"&")")...

i don't think so this will work as wild card character like in COUNTIF. Try this,

=SUMPRODUCT(SUBTOTAL(3,OFFSET(F15,ROW(F15:F143)-ROW(F15),)),(F15:F143<>"N/A")*(F15:F143<>"")*NOT(ISNUMBER(SEARCH("(*/*)",F15:F143))+ISNUMBER(SEARCH("(*-*)",F15:F143))))

Wildcards don't work with comparison operators like <> and =, they are treated as literal asterisks. I suggest you try SEARCH as per Haseeb's suggestion

thanks, Haseeb and DLL for correcting my error - i wasn't aware of that fact.