# Excel - sum values in one column, based on string in another column

Let's say I have the following -- 6 rows, 2 columns.

I want to report in 3 other separate cells, sums:
Total everything from ColB, where ColA has an Aa (example would report 8)
Total everything from ColB, where ColA has an Bb (example would report 10)
Total everything from ColB, where ColA has an Cc (example would report 3)

``````.....A.....B
1....Aa....2
2....Bb....1
3....Cc....3
4....Bb....5
5....Bb....4
6....Aa....6
``````
LVL 9
###### Who is Participating?

Commented:
Does it need to be case-sensitive?

You can use SUMIF which isn't case-sensitive, e.g.

=SUMIF(A1:A6,"Aa",B1:B6)

but, like I say, that will sum col B if A is "AA" or "aa"

For case-sensitive

=SUMPRODUCT(EXACT(A1:A6,"Aa")+0,B1:B6)

regards, barry
0

Commented:
a simple SUMPRODUCT() formula like this would do the trick:

=SUMPRODUCT((\$A\$1:\$A\$6=D1)*(\$B\$1:\$B\$6))

jppinto
0

Commented:
Please take a look at the attached working example.

jppinto
SUMPRODUCT-Example5.xlsx
0

Author Commented:
It doesn't need to be case sensitive.  SumIf seems the simplest solution.  Not sure if/why sumproduct (first answer) would be a better choice?
0

Author Commented:
barryhoudini's is working fine for me, so no other solutions are necessary -- just keeping open until i get a response on last post.
0

Commented:
For a small amount of data it probably doesn't matter which you use but SUMIF is more efficient than SUMPRODUCT (by some distance) so is preferable when you have the choice.

regards, barry
0

Commented:
NFP:

If the data is in the same workbook as the summary, definitely use SUMIF. If not, you would need SUMPRODUCT.
0

Commented:
Don't know why you didn't liked my solution as it was working fine, I even gave you a working file for you to check!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.