sumif function in excel for multiple conditions instead of one
 

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.

sumif function in excel for multiple conditions instead of one
 

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 structu
=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)

"curious44" wrote in message
...
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.

sumif function in excel for multiple conditions instead of one
 

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)


"Ron Coderre" wrote in message
...
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 structu
=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)

"curious44" wrote in message
...
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.

sumif function in excel for multiple conditions instead of one
 

Use SUMPRODUCT. There are a multitude of hits in the archives. Or, google
sumproduct:excel
or use Ron DeBruins addin
http://www.rondebruin.nl/
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"curious44" wrote in message
...
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.