Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I get a word count in Excel XP | Excel Discussion (Misc queries) | |||
Microsoft Excel 2000 Subtotals with Group Name | Excel Worksheet Functions | |||
Looking for a way for excel to count unknown repeating values? | Excel Worksheet Functions | |||
Excel should let you sort by subtotals | Excel Worksheet Functions | |||
excel should have a function to count sheets | Excel Worksheet Functions |