Sumif with 2 criteria
 

Okay, I've done my research, but I can't find the answer to this... I want to
do a sumif with two different criteria, each criteria is in a different
column (like a category, then a sub-category). I've tried the
sumproduct(--..., but I couldn't make it work.

Each number is attached to a category and sub category:

Auto Gas 130
Auto Insurance 25
Food Groceries 200
Auto Insurance 50

So, this is what i've tried: =sumif(a1:b4,and("Auto","Insurance"),c1:c4)

Please help!

Sumif with 2 criteria
 

Hi
Try this

=SUMPRODUCT((A3:A6="Auto")*(B3:B6="Insurance")*C3: C6)

HTH
;-)

"Mike" wrote in message
...
Okay, I've done my research, but I can't find the answer to this... I want
to
do a sumif with two different criteria, each criteria is in a different
column (like a category, then a sub-category). I've tried the
sumproduct(--..., but I couldn't make it work.

Each number is attached to a category and sub category:

Auto Gas 130
Auto Insurance 25
Food Groceries 200
Auto Insurance 50

So, this is what i've tried: =sumif(a1:b4,and("Auto","Insurance"),c1:c4)

Please help!

Sumif with 2 criteria
 

SUMIF can only have 1 criteria. The AND funciton won't work with it. Try
this instead:

=SUMPRODUCT(--(a1:a4="Auto"),--(b1:b4="Insurance"),--(c1:c4))

HTH,
Elkar


"Mike" wrote:

Okay, I've done my research, but I can't find the answer to this... I want to
do a sumif with two different criteria, each criteria is in a different
column (like a category, then a sub-category). I've tried the
sumproduct(--..., but I couldn't make it work.

Each number is attached to a category and sub category:

Auto Gas 130
Auto Insurance 25
Food Groceries 200
Auto Insurance 50

So, this is what i've tried: =sumif(a1:b4,and("Auto","Insurance"),c1:c4)

Please help!

Sumif with 2 criteria
 

If you add an additional column with the formulae...

= A1 & B1 etc

You can then use SUMIF with the concatenated criteria.


--
mrice

Research Scientist with many years of spreadsheet development experience
------------------------------------------------------------------------
mrice's Profile: http://www.excelforum.com/member.php...o&userid=10931
View this thread: http://www.excelforum.com/showthread...hreadid=542659

Sumif with 2 criteria
 

You could also use the DSUM function if you set up your data with headings
and set up a separate table for the criteria (2 rows inc headings and as
many columns as you have criteria)

Hopefully the named ranges in this example are self explnatory

=DSUM(Tbl_With_Heads,3,Crit)

;-)

"Mike" wrote in message
...
Okay, I've done my research, but I can't find the answer to this... I want
to
do a sumif with two different criteria, each criteria is in a different
column (like a category, then a sub-category). I've tried the
sumproduct(--..., but I couldn't make it work.

Each number is attached to a category and sub category:

Auto Gas 130
Auto Insurance 25
Food Groceries 200
Auto Insurance 50

So, this is what i've tried: =sumif(a1:b4,and("Auto","Insurance"),c1:c4)

Please help!