Hi Gilbert
I'm trying to find a fantastic article written by Aladin Akyurek on the
SUMPRODUCT function for you ... but can't find the link (maybe someone else
here has a link to this article?)
However, my limited understanding of it goes like this:
In its basic form the SUMPRODUCT function multiplies number arrays and then
adds the result of each multiplication and returns the answer. Hence the
sumproduct(array1,[array2],[array3],....) structure
BUT
it can also be used as a multi-conditional counting or summing function
using the * operant, which in this case functions as a boolean AND.
Basically we ask excel to evaluate each cell to a True or False state
(whether it matches the criteria or not), and then multiply the result of
that cell with other cells to get a true or false outcome over multiple
cells and then sum or count the results
here's an example
A B C
1 cat blue 10
2 cat green 5
3 dog blue 3
4. cat blue 3
if you wanted the sum of column C where A = cat AND B=blue then
=SUMPRODUCT((A1:A4="cat")*(B1:B4="blue")*C1:C4)
excel evaluates true to 1 and false to 0 (in the example below i use T for
True & F for False)
so it would work through a process like this
=SUMPRODUCT((A1:A4="cat")*(B1:B4="blue")*C1:C4)
=SUMPRODUCT(({cat,cat,dog,cat}="cat")*({blue,green,blue,blue}="blue"))*{10,5
,3,3}) 'check each cell against criteria
=SUMPRODUCT(({T, T, F, T})*({T, F, T,T}))*{10,5,3,3}) 'return T for true /
F for false
=SUMPRODUCT(({1,1,0,1})*({1,0,1,1}))*{10,5,3,3}) 'evaluate T = 1 & F = 0
=SUMPRODUCT(({1*1,1*0,0*1,1*1})*{10,5,3,3}) 'multiply the two columns
together
=SUMPRODUCT(({1,0,0,1})*{10,5,3,3}) 'A1 & B1 meet the condition as does A4 &
B4
=SUMPRODUCT(1*10+0*5+0*3+1*3) 'multiply this with the cells to add
=SUMPRODUCT(10+0+0+3) 'add the cells that meet the condition
=13
Hope this helps - someone else might be able to supply a clearer
explaination for you.
Cheers
julieD
