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)

Thanks for your help!



.....A.....B
1....Aa....2
2....Bb....1
3....Cc....3
4....Bb....5
5....Bb....4
6....Aa....6

Open in new window

LVL 9
blakogreAsked:
Who is Participating?
 
barry houdiniCommented:
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
 
jppintoCommented:
a simple SUMPRODUCT() formula like this would do the trick:

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

jppinto
0
 
jppintoCommented:
Please take a look at the attached working example.

jppinto
SUMPRODUCT-Example5.xlsx
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
blakogreAuthor 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
 
blakogreAuthor 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
 
barry houdiniCommented:
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
 
Rory ArchibaldCommented:
NFP:

If the data is in the same workbook as the summary, definitely use SUMIF. If not, you would need SUMPRODUCT.
0
 
jppintoCommented:
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.

All Courses

From novice to tech pro — start learning today.