Sum values based on value of two columns

**Hond70** · 10-10-2011, 11:01 AM

Hi there,

I have a sheet with activities send by multiple employees. It looks like this:

.....A...............B..........C
1 activity_A....client_A...7
2 activity_B....client_A...1

6 activity_A....client_A....4
7 activity_A....client_D....4

What I need is to sum the hours that matches "activity_A" in column A and "client_A" in column B. In this case row 1 and 6 are matches, so the sum is 7+4=11 hours.

Do I need SUMPRODUCT or VLOOKUP or a combination? I want the formula to be lean, because I need lots of them...

**arlu1201** · 10-10-2011, 11:10 AM

If you are using Excel 2007, this should suffice:

=SUMIFS(C1:C4,A1:A4,A1,B1:B4,B1)

If you are using excel 2003, use sumproduct. You dont need vlookup.

**Andrew-R** · 10-10-2011, 11:10 AM

SUMPRODUCT would probably be the way to go, but if there are lots of them then you might find it a bit slow.

Assuming your data is in rows 1:100 then the SUMPRODCT would be =SUMPRODUCT(C1:C100,--(A1:A100="activity_A"),--(B1:B100="client_A"))

Edited to add: Dur, slow brain day, SUMIFS is a better solution if you're using Excel 2007 or later.