software

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 60amp;gt;
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

Just change the existing subtotal function from SUM to COUNT

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;PARquot; gt; wrote in message
...
gt; I have a spreadsheet grouped by division and region. Region is the inner
gt; group an division is the outer group
gt; Column A B C D E
gt; age 50-54 55-59 60amp;gt;
gt; Region A 50
gt; Region A 51
gt; Region A totals 2 0 0
gt; Region B 60
gt; Region B 65
gt; Region B totals 0 0 2
gt; Division tot 2 0 2
gt;
gt; How do I write a formula in column C3 to subtotal(3,B2:B3) for ages
between
gt; 50 and 54 in cell C4, between ages 55 and 59 in cell D4, etc then total
the
gt; regional totals in the age categories for each division.
gt;
gt; This is a simplistic example there are hundreds of ages in each region and
gt; there are 8 divisions
I know the 3 vs 9. What I can't figure out is how to write the gt;lt; 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 gt;50 but less than 54 argument to work.

quot;Bob Phillipsquot; wrote:

gt; Just change the existing subtotal function from SUM to COUNT
gt;
gt; --
gt; HTH
gt;
gt; Bob Phillips
gt;
gt; (remove nothere from email address if mailing direct)
gt;
gt; quot;PARquot; gt; wrote in message
gt; ...
gt; gt; I have a spreadsheet grouped by division and region. Region is the inner
gt; gt; group an division is the outer group
gt; gt; Column A B C D E
gt; gt; age 50-54 55-59 60amp;gt;
gt; gt; Region A 50
gt; gt; Region A 51
gt; gt; Region A totals 2 0 0
gt; gt; Region B 60
gt; gt; Region B 65
gt; gt; Region B totals 0 0 2
gt; gt; Division tot 2 0 2
gt; gt;
gt; gt; How do I write a formula in column C3 to subtotal(3,B2:B3) for ages
gt; between
gt; gt; 50 and 54 in cell C4, between ages 55 and 59 in cell D4, etc then total
gt; the
gt; gt; regional totals in the age categories for each division.
gt; gt;
gt; gt; This is a simplistic example there are hundreds of ages in each region and
gt; gt; there are 8 divisions
gt;
gt;
gt;

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:$B3gt;=--LEFT(C$1,FIND(quot;-quot;,C$1)-1)),--($B2:$B3lt;=--MID(C$1,F
IND(quot;-quot;,C$1) 1,9)))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;PARquot; gt; wrote in message
...
gt; I know the 3 vs 9. What I can't figure out is how to write the gt;lt; part.
I
gt; want to know is what to write in the region totals cells. The range for
the
gt; first total count in the example is B2:B3 the formula for between ages 50
and
gt; 54 total would go in cell c4. There is nothing in cells C1:C3. I can not
gt; get the gt;50 but less than 54 argument to work.
gt;
gt; quot;Bob Phillipsquot; wrote:
gt;
gt; gt; Just change the existing subtotal function from SUM to COUNT
gt; gt;
gt; gt; --
gt; gt; HTH
gt; gt;
gt; gt; Bob Phillips
gt; gt;
gt; gt; (remove nothere from email address if mailing direct)
gt; gt;
gt; gt; quot;PARquot; gt; wrote in message
gt; gt; ...
gt; gt; gt; I have a spreadsheet grouped by division and region. Region is the
inner
gt; gt; gt; group an division is the outer group
gt; gt; gt; Column A B C D E
gt; gt; gt; age 50-54 55-59 60amp;gt;
gt; gt; gt; Region A 50
gt; gt; gt; Region A 51
gt; gt; gt; Region A totals 2 0 0
gt; gt; gt; Region B 60
gt; gt; gt; Region B 65
gt; gt; gt; Region B totals 0 0 2
gt; gt; gt; Division tot 2 0 2
gt; gt; gt;
gt; gt; gt; How do I write a formula in column C3 to subtotal(3,B2:B3) for ages
gt; gt; between
gt; gt; gt; 50 and 54 in cell C4, between ages 55 and 59 in cell D4, etc then
total
gt; gt; the
gt; gt; gt; regional totals in the age categories for each division.
gt; gt; gt;
gt; gt; gt; This is a simplistic example there are hundreds of ages in each region
and
gt; gt; gt; there are 8 divisions
gt; gt;
gt; gt;
gt; gt;