SumProduct Not Calculating

Hi,

I created a sumproduct formula to calcualte the averages, but it doesn't work. I think I got the formula right. Can some please tell me what is wrong with it? Attached is the formula, in highlighted yellow cells.

Please help,
Dee

Your syntax is wrong...

Here, try this in B4 and adopt for other:

=SUMPRODUCT(--($A$9:$A$39>=$B$1),--($A$9:$A$39<=$C$1),$B$9:$B$39)/SUMPRODUCT(--($A$9:$A$39>=$B$1),--($A$9:$A$39<=$C$1),--($B$9:$B$39<>""))

Thanks Zbor. It works! Can you tell me what is the difference between the -- and the * symbols in the SumProduction function?

Using -- is just slightly better.

Here is what Donkeyote said about it:

If you use the above method then you can include the header row without issue... but double unary approach means each array must identical in terms of dimensions

If you use the multiplication method (which does not nec. have this requirement) then it follows given the explicit coercion taking place your summation range should not include non-numerics.

Basic example:

=SUMPRODUCT(--({TRUE,FALSE,TRUE}),--({TRUE,TRUE,TRUE}),{"a",1,2}) -> 2

the "a" in the summation range will be ignored in similar vein to a SUMIF ignoring non-numerics in sum_range (no explicit coercion taking place)

Conversely

=SUMPRODUCT({TRUE,FALSE,TRUE}*{TRUE,TRUE,TRUE}*{"a",1,2}) -> #VALUE!

the "a" here causes an issue as it's explicitly coerced given * - hence 1*"a" -> #VALUE!

For more info. on SUMPRODUCT see Bob Phillips' white paper: http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Needs to be "array entered" but for averages why not use AVERAGE?

=AVERAGE(IF($A$9:$A$39>=$B$1,IF(A$9:$A$39<=$C$1,IF($B$9:$B$39<>"",$B$9:$B$39))))