For a simple, single-condition test, I much prefer sumif:
=sumif(a:a,"Income",b:b). But sumproduct does offer a great deal of
flexibility for more complicated conditions. For some great information on
how it works, and why, check
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
"windsurferLA" wrote:
Im trying to understand how the various sumif and sumproduct functions
work, because I want to sum up data based format of the cell containing
the data.
Please note that I'm using EXCEL97 with all of the latest Microsoft
patches.
If seen the following formula used to sum the values in column €śB€ť where
column €śA€ť contains the term €śincome€ť.
=SUMPRODUCT((A2:A6="Income")*(B2:E6))
The fact that it works implies that the test
(A2:A6="Income")
returns a value of unity when €śtrue€ť rather than the text €śtrue€ť.
The SUMPRODUCT FUNCTION then performs the equivalent of a matrix
multiplication and returns the sum.
I find it rather strange that the test returns the value unity. HOW COME?
Ive also observed that if I place the function formula.
=Cell(€śformat€ť,B2:E6)
any place else on the worksheet,
the SUMPRODUCT formula shown above returns €ś#value€ť.
WHY SHOULD A FORMULA IN ONE PART OF WORKSHEET IMPACT RESULTS IN ANOTHER?
Ive also observed that if I use the formula
=Cell(€śformat€ť,B2:E6)
and then change the formatting of a cell within the range being tested,
such as B2,
the output of =Cell(€śformat€ť,B2:E6) does not change until I force a
recalculation even though the sheet is set for automatic recalc. WHY?