SUMIF with AND for range and criteria
 

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.


--
davidm_ba
------------------------------------------------------------------------
davidm_ba's Profile: http://www.excelforum.com/member.php...o&userid=25811
View this thread: http://www.excelforum.com/showthread...hreadid=392096

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

--
HTH

Bob Phillips

"davidm_ba" wrote
in message ...

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.


--
davidm_ba
------------------------------------------------------------------------
davidm_ba's Profile:
http://www.excelforum.com/member.php...o&userid=25811
View this thread: http://www.excelforum.com/showthread...hreadid=392096

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!D1:D4)

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



davidm_ba Wrote:
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.


--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=392096

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.


--
davidm_ba
------------------------------------------------------------------------
davidm_ba's Profile: http://www.excelforum.com/member.php...o&userid=25811
View this thread: http://www.excelforum.com/showthread...hreadid=392096