P
Patricia
Would appreciate knowing if anyone knows how to do this, and if so what is
the syntax. Thanks.
the syntax. Thanks.
B
Bernard Liengme
No, but you can use multiple criteria with SUMPRODUCT.
Example =SUMPRODUCT(--(A1:A100="Jack"),--(B1:B100="Car"),C1:C100=5, D1
100)
will sum d1:d100 cells provides corresponding A cell has Jack, B cell has
Cars and C cells equals 5
See also http://contextures.com/xlFunctions01.html#SumProduct
Example =SUMPRODUCT(--(A1:A100="Jack"),--(B1:B100="Car"),C1:C100=5, D1
100)will sum d1:d100 cells provides corresponding A cell has Jack, B cell has
Cars and C cells equals 5
See also http://contextures.com/xlFunctions01.html#SumProduct
A
Alan
No, you can only use one criteria with SUMIF, but with SUMPRODUCT there is
no limit to how many criteria that can be used apart from the fact that it
is a volatile function, and if used in large ranges it will take a
significant time to calculate, more so if it's looking for more than one
criteria.
If you do need to use large (several hundreds or thousands) of SUMPRODUCT
formulas then probably the best way to go is a Pivot Table.
Check out this link, it'll take a while to read but it really is worth the
effort,
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
Regards,
Alan.
no limit to how many criteria that can be used apart from the fact that it
is a volatile function, and if used in large ranges it will take a
significant time to calculate, more so if it's looking for more than one
criteria.
If you do need to use large (several hundreds or thousands) of SUMPRODUCT
formulas then probably the best way to go is a Pivot Table.
Check out this link, it'll take a while to read but it really is worth the
effort,
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
Regards,
Alan.