Filter Subtotal Problems

[Jarnell]

I am having difficulty applying sub-total to a filtered worksheet. My
worksheet has 11 columns (6 shown in the example below). At the bottom of
the worksheet I need to be able to have multiple calculations run based on
the filters. The calculations are on text in ColK and need to give a total
utilizing the words in parenthese. These calculations need to update
automatically when the filter is changed on ColD or on ColJ (status). I am
at a loss.

ColA ColB ColC ColD ColE-ColJ ColK
2244 1 0 12/3/09 Misc. Info Control Zone Use
(Utility)
2246 1 0 12/17/09 Clear Zone
(Utility)
P2242 4 0 12/1/09 Superelevation
(Design)
2215 2 1 12/9/09 Vertical
Clearance (Design)

 

[T. Valko]

It's not clear what you want to do.

The calculations are on text in ColK and
need to give a total utilizing the words in parenthese.

Ok, so just post an example of what's in column K (only, the other columns
are irrelevant) and what exaclty you want to count.

 

[Jarnell]

Yes the calculations are on text. So in a large spreadsheet that has over
5000 records, we filter down for a weekly report, a monthly report and a
status (pending, denied, approved). In the reports we need to know how many
"Utility" or "Design" submittals.

ColK
Control Zone Use (Utility)
Stopping Sight Distance (Design)
Clear Zone (Utility)
Clear Zone (Utility)
Bridge Width (Design)
Shoulder Width (Design)
Structural Capacity (Design)
Vertical Alignment (Design)
Design Speed (Variation)

Thanks.

 

[T. Valko]

Ok, assume the full unfiltered range is K5:K100.

Use a cell to hold the criteria to count for:

A1 = Design

=SUMPRODUCT(SUBTOTAL(3,OFFSET(K5:K100,ROW(K5:K100)-ROW(K5),0,1)),--(ISNUMBER(SEARCH("("&A1&")",K5:K100))))

 

[Jarnell]

You ROCK!!!!!! This worked withought any problems.

 

[T. Valko]

You're welcome. Thanks for the feedback!