SUMIF with AND for range and criteria

[davidm_ba]

Hi,

I a bit of an Excel newbie, and can't figure this one out. Maybe it's
easy for someone out there?

I need to be able to do a SUMIF based on 2 seperate columns in order to
put a sum of values from one sheet into a summary on another.

For example,

Sheet1
A B C
John Smith 40
Joe Bloggs 38
John Smith 24
Jane Smith 36

Sheet2
X Y Z
John Smith 64


So in Sheet2, the total (col. Z) is calculated from Sheet 1 col. C,
based on BOTH A matching X and B matching Y.


Thanks for any help you can give.

Dave.

 

[Bob Phillips]

=SUMPRODUCT(--(A2:A100="John"),--(B2:B100="Smith"),C2:C100)

 

[Bryan Hessey]

It's not really clever, but I would run two helper columns, a new column
C on sheet1 =A1&" "&B1 and a new column Z on sheet2 =X1&" "&Y1
this would give me a safety check on the match and an easy SumIf.

and the total column, now in AA, then reads

=SUMIF(Sheet1!C1:Sheet1!C4,Z1:Z4,Sheet1!D14)

I presume that you know to formula-drag down the column for the extent
of your data.

 

[davidm_ba]

Thanks Bryan,

It doesn't have to be clever, as long as it works, and that seems like
a straight-forward way to do it.

Dave.