Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
but < subtotal (3,...) or countif
I have a spreadsheet grouped by division and region. Region is the inner
group an division is the outer group Column A B C D E age 50-54 55-59 60& Region A 50 Region A 51 Region A totals 2 0 0 Region B 60 Region B 65 Region B totals 0 0 2 Division tot 2 0 2 How do I write a formula in column C3 to subtotal(3,B2:B3) for ages between 50 and 54 in cell C4, between ages 55 and 59 in cell D4, etc then total the regional totals in the age categories for each division. This is a simplistic example there are hundreds of ages in each region and there are 8 divisions |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
but < subtotal (3,...) or countif
Just change the existing subtotal function from SUM to COUNT
-- HTH Bob Phillips (remove nothere from email address if mailing direct) "PAR" wrote in message ... I have a spreadsheet grouped by division and region. Region is the inner group an division is the outer group Column A B C D E age 50-54 55-59 60& Region A 50 Region A 51 Region A totals 2 0 0 Region B 60 Region B 65 Region B totals 0 0 2 Division tot 2 0 2 How do I write a formula in column C3 to subtotal(3,B2:B3) for ages between 50 and 54 in cell C4, between ages 55 and 59 in cell D4, etc then total the regional totals in the age categories for each division. This is a simplistic example there are hundreds of ages in each region and there are 8 divisions |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
but < subtotal (3,...) or countif
I know the 3 vs 9. What I can't figure out is how to write the < part. I
want to know is what to write in the region totals cells. The range for the first total count in the example is B2:B3 the formula for between ages 50 and 54 total would go in cell c4. There is nothing in cells C1:C3. I can not get the 50 but less than 54 argument to work. "Bob Phillips" wrote: Just change the existing subtotal function from SUM to COUNT -- HTH Bob Phillips (remove nothere from email address if mailing direct) "PAR" wrote in message ... I have a spreadsheet grouped by division and region. Region is the inner group an division is the outer group Column A B C D E age 50-54 55-59 60& Region A 50 Region A 51 Region A totals 2 0 0 Region B 60 Region B 65 Region B totals 0 0 2 Division tot 2 0 2 How do I write a formula in column C3 to subtotal(3,B2:B3) for ages between 50 and 54 in cell C4, between ages 55 and 59 in cell D4, etc then total the regional totals in the age categories for each division. This is a simplistic example there are hundreds of ages in each region and there are 8 divisions |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
but < subtotal (3,...) or countif
I see now, the layout confused me as I thought the numbers were in the
appropriate columns. If you change the third heading to 60-99, you can use =SUMPRODUCT(--($B2:$B3=--LEFT(C$1,FIND("-",C$1)-1)),--($B2:$B3<=--MID(C$1,F IND("-",C$1)+1,9))) -- HTH Bob Phillips (remove nothere from email address if mailing direct) "PAR" wrote in message ... I know the 3 vs 9. What I can't figure out is how to write the < part. I want to know is what to write in the region totals cells. The range for the first total count in the example is B2:B3 the formula for between ages 50 and 54 total would go in cell c4. There is nothing in cells C1:C3. I can not get the 50 but less than 54 argument to work. "Bob Phillips" wrote: Just change the existing subtotal function from SUM to COUNT -- HTH Bob Phillips (remove nothere from email address if mailing direct) "PAR" wrote in message ... I have a spreadsheet grouped by division and region. Region is the inner group an division is the outer group Column A B C D E age 50-54 55-59 60& Region A 50 Region A 51 Region A totals 2 0 0 Region B 60 Region B 65 Region B totals 0 0 2 Division tot 2 0 2 How do I write a formula in column C3 to subtotal(3,B2:B3) for ages between 50 and 54 in cell C4, between ages 55 and 59 in cell D4, etc then total the regional totals in the age categories for each division. This is a simplistic example there are hundreds of ages in each region and there are 8 divisions |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Subtotal on Autofilter with Countif | Excel Worksheet Functions | |||
Embed a countif function in subtotal function? | Excel Worksheet Functions | |||
COUNTIF Subtotal Function? | Excel Discussion (Misc queries) | |||
How do I use COUNTIF in a SUBTOTAL function to differentiate the . | Excel Worksheet Functions | |||
Subtotal And Countif | Excel Discussion (Misc queries) |