Filter Subtotal Problems
 

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)

Filter Subtotal Problems
 

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.

--
Biff
Microsoft Excel MVP


"Jarnell" wrote in message
...
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)

Filter Subtotal Problems
 

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" wrote:

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.

--
Biff
Microsoft Excel MVP


"Jarnell" wrote in message
...
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)




.

Filter Subtotal Problems
 

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))))

--
Biff
Microsoft Excel MVP


"Jarnell" wrote in message
...
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" wrote:

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.

--
Biff
Microsoft Excel MVP


"Jarnell" wrote in message
...
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)




.

Filter Subtotal Problems
 

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

"T. Valko" wrote:

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))))

--
Biff
Microsoft Excel MVP


"Jarnell" wrote in message
...
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" wrote:

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.

--
Biff
Microsoft Excel MVP


"Jarnell" wrote in message
...
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)




.



.

Filter Subtotal Problems
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Jarnell" wrote in message
...
You ROCK!!!!!! This worked withought any problems.

"T. Valko" wrote:

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))))

--
Biff
Microsoft Excel MVP


"Jarnell" wrote in message
...
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" wrote:

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.

--
Biff
Microsoft Excel MVP


"Jarnell" wrote in message
...
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)




.



.