How do I count just the subtotals in Excel?
 

Hi,
I've got a worksheet that summarizes customer, date, and contract number
(over 9000 rows). I can get subtotals by using the Subtotal function of
Excel. However, I also need to count how many (within the subtotals only) are
over, under, or equal to say $100. Have looked all over and can't seem to
find a formula that ONLY counts the subtotals. All formulas seem to count
within the 9000 rows. Please help!

How do I count just the subtotals in Excel?
 

Here's a formula

=SUMPRODUCT(--(RIGHT(A1:A23,5)="Total"),--(RIGHT(A1:A23,11)<"Grand
Total"),--(B1:B23=100))

--

HTH

RP

"Wngzro07" wrote in message
...
Hi,
I've got a worksheet that summarizes customer, date, and contract number
(over 9000 rows). I can get subtotals by using the Subtotal function of
Excel. However, I also need to count how many (within the subtotals only)
are
over, under, or equal to say $100. Have looked all over and can't seem to
find a formula that ONLY counts the subtotals. All formulas seem to count
within the 9000 rows. Please help!

How do I count just the subtotals in Excel?
 

Hi Bob,
Thanks for the quick response. I'm such a newbie that I don't fully
understand the formula below. Perhaps this specific subset of data can help
as below is exactlly what i have. How do i get a formula to count the number
of customers that have subtotal values of greater than $1000? Thanks in
advance for your help!

Customer Name Date Contract Value
AAA 12/31/2005 $500
AAA 12/31/2005 $675
AAA Total $1175
ABBOTT LABORATORIES 12/31/2005 $150
ABBOTT LABORATORIES 12/31/2005 $200
ABBOTT LABORATORIES 12/31/2005 $250
ABBOTT LABORATORIES 12/31/2005 $300
ABBOTT LABORATORIES Total $900


"Bob Phillips" wrote:

Here's a formula

=SUMPRODUCT(--(RIGHT(A1:A23,5)="Total"),--(RIGHT(A1:A23,11)<"Grand
Total"),--(B1:B23=100))

--

HTH

RP

"Wngzro07" wrote in message
...
Hi,
I've got a worksheet that summarizes customer, date, and contract number
(over 9000 rows). I can get subtotals by using the Subtotal function of
Excel. However, I also need to count how many (within the subtotals only)
are
over, under, or equal to say $100. Have looked all over and can't seem to
find a formula that ONLY counts the subtotals. All formulas seem to count
within the 9000 rows. Please help!

How do I count just the subtotals in Excel?
 

Try...

=SUMPRODUCT(1-SUBTOTAL(3,OFFSET(C2:C9,ROW(C2:C9)-ROW(C2),0,1)),--(C2:C91
000))

Note that the range excludes the last row containing the 'Grand Total'.

Hope this helps!

In article ,
"Wngzro07" wrote:

Hi Bob,
Thanks for the quick response. I'm such a newbie that I don't fully
understand the formula below. Perhaps this specific subset of data can help
as below is exactlly what i have. How do i get a formula to count the number
of customers that have subtotal values of greater than $1000? Thanks in
advance for your help!

Customer Name Date Contract Value
AAA 12/31/2005 $500
AAA 12/31/2005 $675
AAA Total $1175
ABBOTT LABORATORIES 12/31/2005 $150
ABBOTT LABORATORIES 12/31/2005 $200
ABBOTT LABORATORIES 12/31/2005 $250
ABBOTT LABORATORIES 12/31/2005 $300
ABBOTT LABORATORIES Total $900

How do I count just the subtotals in Excel?
 

Neat way to reverse the values. I like it <G

Bob

"Domenic" wrote in message
...
Try...

=SUMPRODUCT(1-SUBTOTAL(3,OFFSET(C2:C9,ROW(C2:C9)-ROW(C2),0,1)),--(C2:C91
000))

Note that the range excludes the last row containing the 'Grand Total'.

Hope this helps!

In article ,
"Wngzro07" wrote:

Hi Bob,
Thanks for the quick response. I'm such a newbie that I don't fully
understand the formula below. Perhaps this specific subset of data can
help
as below is exactlly what i have. How do i get a formula to count the
number
of customers that have subtotal values of greater than $1000? Thanks in
advance for your help!

Customer Name Date Contract Value
AAA 12/31/2005 $500
AAA 12/31/2005 $675
AAA Total $1175
ABBOTT LABORATORIES 12/31/2005 $150
ABBOTT LABORATORIES 12/31/2005 $200
ABBOTT LABORATORIES 12/31/2005 $250
ABBOTT LABORATORIES 12/31/2005 $300
ABBOTT LABORATORIES Total $900