sumif function in excel for multiple conditions instead of one

[curious44]

I have 3 columns of data. I need to sum the 3rd column(i.e. sum range) if
conditions are met in the first 2 columns. An example would be this. Column
1,2,3 are the type of music(jazz,pop, blues etc), media(cd, dvd,casette) and
Profit respectively.
How to i write the function or is there another function i could use in
excel for adding up the Profit(column 3) for if the music type is jazz(column
1) and cd(column2). The sumif function only allows you to specify 1
condition. ie. if the music type is jazz, then sum the profit. Please advise,
your help would be very much appreciated.

 

[Ron Coderre]

Try this:

If
A2:A20 contains Music
B2:B20 contains Media
C2:C20 contains Profit

D1: Jazz
E1: CD

This formula returns the total profit for that combination:
=SUMPRODUCT((A2:A20=E1)*(B2:B20=F1)*C2:C20)

and this is an alternative SUMPRODUCT structure:
=SUMPRODUCT(--(A2:A20=E1),--(B2:B20=F1),C2:C20)

Mostly, they return the same value, but sometimes one works
where the other fails.

Is that something you can work with?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

 

[Ron Coderre]

Typos...sorry

D1: Jazz
E1: CD

should be:
E1: Jazz
F1: CD

....so the formulas can work.

--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

 

[Don Guillett]

Use SUMPRODUCT. There are a multitude of hits in the archives. Or, google
sumproduct:excel
or use Ron DeBruins addin
http://www.rondebruin.nl/