SUMIF based on 2 columns instead of 1?
 

I have columns Person ("Adam", "Bob", "Charlie"), Day ("Monday",
"Tuesday", etc), Total. I'd like to SUMIF based on BOTH Person and Day
(e.g. sum for "Bob" and "Tuesday"). I'd rather not use PivotTables,
because (1) this is pretty simple and (2) people here are scared of
PivotTables. Can i use SUMIF based on 2 columns, either by
concatenating the text or with an array? Thanks.

SUMIF based on 2 columns instead of 1?
 

You are better off with sumproduct. Check out this site for further details...

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Bookmark this site. It is a great reference...
--
HTH...

Jim Thomlinson


" wrote:

I have columns Person ("Adam", "Bob", "Charlie"), Day ("Monday",
"Tuesday", etc), Total. I'd like to SUMIF based on BOTH Person and Day
(e.g. sum for "Bob" and "Tuesday"). I'd rather not use PivotTables,
because (1) this is pretty simple and (2) people here are scared of
PivotTables. Can i use SUMIF based on 2 columns, either by
concatenating the text or with an array? Thanks.

SUMIF based on 2 columns instead of 1?
 

Hi there,

Try something like ..

=SUMPRODUCT((A1:A100="Bob")*(B1:B100="Tuesday"),C1 :C100)

Where column A has your names, column B has your days and column C has the
associated value you wish to sum.

HTH

--
Regards,
Zack Barresse, aka firefytr


wrote in message
oups.com...
I have columns Person ("Adam", "Bob", "Charlie"), Day ("Monday",
"Tuesday", etc), Total. I'd like to SUMIF based on BOTH Person and Day
(e.g. sum for "Bob" and "Tuesday"). I'd rather not use PivotTables,
because (1) this is pretty simple and (2) people here are scared of
PivotTables. Can i use SUMIF based on 2 columns, either by
concatenating the text or with an array? Thanks.

SUMIF based on 2 columns instead of 1?
 

You can use SUMPRODUCT, e.g.

SUMPRODUCT(--(A2:A100="Bob"),--(B2:B100="Tuesday"),C2:C100)


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=540054

SUMIF based on 2 columns instead of 1?
 

Have you tried an array function, something like

=(SUM((A1:A3="Bob")*(B1:B3="Tuesday")))

then use ctrl shift enter to set it as array