formula to count subtotaled rows

[Randy]

I use the subtotal worksheet function extensively, and
need to count the unique rows in my subtotaled ranges,
based on the columns of subtotaled data. Is there a
formula I could apply to a cell to count the number of
occurrences in a given column of the word "Total" that is
appended to rows of summary data?

Much thanks for you help!
R

 

[Peo Sjoblom]

Try

=COUNTIF(A:A,"*Total*")

note that it will count the total in the grand total as well so make sure
you either deduct 1 or not include the grand total cell

--

Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)

 

[Randy]

Thanks Peo.

Is there a way to make the result in the COUNTIF formula
(or any other formula that would satisfy this situation)
responsive to selections made using the autofilter?
similar to behavior when using the subtotaled function.

I appreciate your help,
Randy

 

[Aladin Akyurek]

=SUMPRODUCT(SUBTOTAL(3,OFFSET(Range,ROW(Range)-MIN(ROW(Range)),,1)),--(ISNUM
BER(SEARCH("Total",Range)))

Range refers to the range where the substring "Total" supposed to occur.

[...]

 

[Peo Sjoblom]

I assume you know that =SUBTOTAL(3,Range) counts visible cells using
autofilter,
you could obviously use custom filtering (where row contains criteria) then
count the visible cells
If that is not possible you can use

=SUMPRODUCT(--(A2:A200>5),SUBTOTAL(3,OFFSET(A2,ROW(A2:A200)-MIN(ROW(A2:A200)
),,)))

would be the same as COUNTIF(A2:A200,">5")

or with text

=SUMPRODUCT(--(A2:A200="text"),SUBTOTAL(3,OFFSET(A2,ROW(A2:A200)-MIN(ROW(A2:
A200)),,)))

or contains text string

=SUMPRODUCT(--(ISNUMBER(SEARCH("text",A2:A200))),SUBTOTAL(3,OFFSET(A2,ROW(A2
:A200)-MIN(ROW(A2:A200)),,)))



--

Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)