Subtotal

[brummingham]

Morning,

I am using Subtotal within my Excel spreadsheet to count data in a filtered spreadsheet.

I am using the below formula to capture specific words when my sheet is filtered and this works fine.

=SUMPRODUCT(SUBTOTAL(3,OFFSET(O1,ROW(O12:O103)-ROW(O1),0)),(O12:O103="North")+0)

However, I want another formula to work for when filtering data to cell containing any text and not a specific word like North. I thought I could just pit "*" but this doesn't seem to work, can anyone help?

Regards,

Chris

[XLent]

when you say any text are you explicitly excluding other data types - like numerics, booleans etc?

If not - you can use a LEN test - i.e. replace (O12:O103="North")+0 with SIGN(LEN(O12:O103))
If yes - i.e. text only - then you could replace (O12:O103="North")+0 with ISTEXT(O12:O103)*SIGN(LEN(O12:O103))

[brummingham]

Sorry I don't think I made myself clear.

The formula I posted basically looks for the word "North" in a column. However, I want to run the formula on another column which will look for any cell containing text (doesn't matter on what text).

[XLent]

yes, I was able to deduce what your current formula does, the suggestions in my prior post remain valid - simply change the range reference from O to suit, e.g.:

Please Login or Register  to view this content.

the above would count number of (filtered) cells in Col P which contained text strings >= 1 char