Solved: Unorganized Summing

[kurt0101]

Hi. I have some data that is organized like so:
(just an example)
A2009 5
A2009 10
B2009 15
C2009 18
C2009 23
D2009 25
D2009 27
etc.
The point is that there are two columns
the first column has a letter followed by a 4 digit number.
how many A2009's, B2009's etc appear in a row(repeat) is variable.
the second column has a number from 0->infinity

What I need to do is add the numbers in column two for EACH letter/4 digit number in column one.
So the sum for A2009 would be 15(10+5)
the sum for B2009 would be 15
the sum for C2009 would be 41 (18+23)
the sum for D2009 would be 52 (25+27)

So of course I do not want to do this by hand because I may have many thousands of rows ranging from A2009->Z2009 in column A
But I only need the sum for A2009, B2009 etc individually.

Hope this makes sense.

I have attached a file if my explanation does not make sense.

Thank you!

[GTO]

Greeting Kurt,

In your msg, you have:
A2009 5
A2009 10
...etc

But in the workbook, it appears that you want a total for each group like:
A2009 5
A2010 10

If so (and I get a bit lucky), try:

=SUMPRODUCT((LEFT(B1:B256,2)="A2")*(C1:C256))
=SUMPRODUCT((LEFT(B1:B256,2)="B2")*(C1:C256))
=SUMPRODUCT((LEFT(B1:B256,2)="C2")*(C1:C256))

...where "A2", "B2", etc, are the left two characters to identify the group.

Of course adjust the B256 and C256 to where the last row might be, but if I recall correctly, I do not think that you can specify the entire column*.

I would suggest reading the article(s) in the sub forum SUMPRODUCT And Other Array Functions listed at the top. There's some awfully nifty stuff that can be done w/SUMPRODUCT :-)

Anyways, hope this helps,

Mark

[Bob Phillips]

=SUMIF(B:B,"A2*",C:C)
=SUMIF(B:B,"B2*",C:C)
etc.

[kurt0101]

Thank you for your post. It helped a lot!

[GTO]

=SUMIF(B:B,"A2*",C:C)
=SUMIF(B:B,"B2*",C:C)
etc.

Well shazbutt! Evidently there's cool stuff w/SUMIF too. Thanks Bob, I had no idea you could do that (wildcard).

Mark

[Bob Phillips]

Indeed you can, and SUMIF is so much more efficient than SUMPRODUCT.