[CST[_2_]]

Hi All,

I have the following data which will always be sorted/grouped. I
basically want to sum all the A's, B's, and C's values (like a
subtotal)

Column Value
A 2
A 3
A 1
B 3
B 4
B 5
C 6
C 1
C 9

I want to find the the First and last row number A and also for B, so
Start A = 1, End A = 3
Start B = 4, End B = 6
Start C = 7, End C = 9

This issue I have is the column values can differ. For example,
Column
A
A
A
C
C
C

So,
Start A = 1, End A = 3
Start C = 4, End C = 6

Since I know the start and end of each grouping, I can then sum them.
If the column alwas had A,B,and C, then I know how to do this,
however, since it is dynamic, I am not sure how to write code dynamic
enough to handle this.

TIA

[Don P]

If the end results are totaling numbers associated with A, B, and C, why not
use a pivot table?

This site will get you started:
http://www.contextures.com/xlPivot01.html


Don Pistulka


"CST" wrote in message
om...
Hi All,

I have the following data which will always be sorted/grouped. I
basically want to sum all the A's, B's, and C's values (like a
subtotal)

Column Value
A 2
A 3
A 1
B 3
B 4
B 5
C 6
C 1
C 9

I want to find the the First and last row number A and also for B, so
Start A = 1, End A = 3
Start B = 4, End B = 6
Start C = 7, End C = 9

This issue I have is the column values can differ. For example,
Column
A
A
A
C
C
C

So,
Start A = 1, End A = 3
Start C = 4, End C = 6

Since I know the start and end of each grouping, I can then sum them.
If the column alwas had A,B,and C, then I know how to do this,
however, since it is dynamic, I am not sure how to write code dynamic
enough to handle this.

TIA

[Tom Ogilvy]

sumA =application.Sumif(Columns(1),"A",Columns(2))

SumB = application.Sumif(Columns(1),"B",Columns(2))

SumC = application.Sumif(Columns(1),"C",Columns(2))


will do it without sorting or grouping.

Regards,
Tom Ogilvy

"CST" wrote in message
om...
Hi All,

I have the following data which will always be sorted/grouped. I
basically want to sum all the A's, B's, and C's values (like a
subtotal)

Column Value
A 2
A 3
A 1
B 3
B 4
B 5
C 6
C 1
C 9

I want to find the the First and last row number A and also for B, so
Start A = 1, End A = 3
Start B = 4, End B = 6
Start C = 7, End C = 9

This issue I have is the column values can differ. For example,
Column
A
A
A
C
C
C

So,
Start A = 1, End A = 3
Start C = 4, End C = 6

Since I know the start and end of each grouping, I can then sum them.
If the column alwas had A,B,and C, then I know how to do this,
however, since it is dynamic, I am not sure how to write code dynamic
enough to handle this.

TIA

[Henrik Wendel]

If the list is in A1 to A9 and values are in B1:B9

then this formula will do what you ask for.

=SUMPRODUCT((A1:A9="A")*1,B1:B9)

Replace the "A" with "B" to sum the rows with B in column A...

Wendel


"CST" skrev i meddelandet
om...
Hi All,

I have the following data which will always be sorted/grouped. I
basically want to sum all the A's, B's, and C's values (like a
subtotal)

Column Value
A 2
A 3
A 1
B 3
B 4
B 5
C 6
C 1
C 9

I want to find the the First and last row number A and also for B, so
Start A = 1, End A = 3
Start B = 4, End B = 6
Start C = 7, End C = 9

This issue I have is the column values can differ. For example,
Column
A
A
A
C
C
C

So,
Start A = 1, End A = 3
Start C = 4, End C = 6

Since I know the start and end of each grouping, I can then sum them.
If the column alwas had A,B,and C, then I know how to do this,
however, since it is dynamic, I am not sure how to write code dynamic
enough to handle this.

TIA

[Chong Moua]

Hi,

You might want to use the SUMIF function...
=SUMIF(A:A,"A",B:B) will sum anything in column A that
is "A" using the values in column B.

Hope this helps...

Chong Moua

-----Original Message-----
Hi All,

I have the following data which will always be
sorted/grouped. I
basically want to sum all the A's, B's, and C's values
(like a
subtotal)

Column Value
A 2
A 3
A 1
B 3
B 4
B 5
C 6
C 1
C 9

I want to find the the First and last row number A and
also for B, so
Start A = 1, End A = 3
Start B = 4, End B = 6
Start C = 7, End C = 9

This issue I have is the column values can differ. For
example,
Column
A
A
A
C
C
C

So,
Start A = 1, End A = 3
Start C = 4, End C = 6

Since I know the start and end of each grouping, I can
then sum them.
If the column alwas had A,B,and C, then I know how to do
this,
however, since it is dynamic, I am not sure how to write
code dynamic
enough to handle this.

TIA
.

[Con Tang[_2_]]

Sweet!!! This worked! Thank you sooooooo much!



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!