sum if with multiple criteria

[djk]

how can I use multiple criteria and the sum if function.

how could I sum for "red apples"?

ColA ColB ColC
red apples 5
green apples 2
baby carrots 1
red apples 3

Thanks

 

[JLatham]

If you have Excel 2007 you could use the new SUMIFS() function. If you're
using an older version, as most of us [probably] are, then the SUMPRODUCT()
function is what you need:

=SUMPRODUCT(--(A2:A5="red"),--(B2:B5="apples"),C2:C5)

That assumes the first entry is in row 2 and that row 1 probably contains
column labels.

You could even set it up to give you product totals in another area, by
entering the different product descriptions in 2 columns and putting the
formula next to them, as
Col G Col H Col I
red apples =SUMPRODUCT(--(A$2:A$5=G2),--(B$2:B$5=H2),C$2:C$5)
green apples =SUMPRODUCT(--(A$2:A$5=G3),--(B$2:B$5=H3),C$2:C$5)
baby carrots =SUMPRODUCT(--(A$2:A$5=G4),--(B$2:B$5=H4),C$2:C$5)

Note that after you enter the formula in I2, it fills down very nicely.

 

[djk]

thanks, I'm trying your suggestion, but still struggling

what if both red and apples are variables or cells I'd like to point.
assume multple colors in ColA and multiple fruits in ColB in the range, but
the red and apples would be a defined cell on another worksheet where I am
looking for totals.
--
djk

If you have Excel 2007 you could use the new SUMIFS() function. If you're
using an older version, as most of us [probably] are, then the SUMPRODUCT()
function is what you need:

=SUMPRODUCT(--(A2:A5="red"),--(B2:B5="apples"),C2:C5)

That assumes the first entry is in row 2 and that row 1 probably contains
column labels.

You could even set it up to give you product totals in another area, by
entering the different product descriptions in 2 columns and putting the
formula next to them, as
Col G Col H Col I
red apples =SUMPRODUCT(--(A$2:A$5=G2),--(B$2:B$5=H2),C$2:C$5)
green apples =SUMPRODUCT(--(A$2:A$5=G3),--(B$2:B$5=H3),C$2:C$5)
baby carrots =SUMPRODUCT(--(A$2:A$5=G4),--(B$2:B$5=H4),C$2:C$5)

Note that after you enter the formula in I2, it fills down very nicely.

how can I use multiple criteria and the sum if function.

how could I sum for "red apples"?

ColA ColB ColC
red apples 5
green apples 2
baby carrots 1
red apples 3

Thanks

 

[JLatham]

Perhaps working examples will help you some more.
Click this link and save the file to your system and examine the 1st 2
worksheets in it for examples of use of the formula, both on the same sheet,
and in conjunction with lists on the second sheet.
http://www.jlathamsite.com/uploads/djk_SumproductExamples.xls

If you have any questions about it, let me know.

thanks, I'm trying your suggestion, but still struggling

what if both red and apples are variables or cells I'd like to point.
assume multple colors in ColA and multiple fruits in ColB in the range, but
the red and apples would be a defined cell on another worksheet where I am
looking for totals.
--
djk

If you have Excel 2007 you could use the new SUMIFS() function. If you're
using an older version, as most of us [probably] are, then the SUMPRODUCT()
function is what you need:

=SUMPRODUCT(--(A2:A5="red"),--(B2:B5="apples"),C2:C5)

That assumes the first entry is in row 2 and that row 1 probably contains
column labels.

You could even set it up to give you product totals in another area, by
entering the different product descriptions in 2 columns and putting the
formula next to them, as
Col G Col H Col I
red apples =SUMPRODUCT(--(A$2:A$5=G2),--(B$2:B$5=H2),C$2:C$5)
green apples =SUMPRODUCT(--(A$2:A$5=G3),--(B$2:B$5=H3),C$2:C$5)
baby carrots =SUMPRODUCT(--(A$2:A$5=G4),--(B$2:B$5=H4),C$2:C$5)

Note that after you enter the formula in I2, it fills down very nicely.

how can I use multiple criteria and the sum if function.

how could I sum for "red apples"?

ColA ColB ColC
red apples 5
green apples 2
baby carrots 1
red apples 3

Thanks