Sumproduct syntax question

[ocuhcs]

This is my first attempt a using sumproduct and testing by rows and columns
together.

My goal is to:
Sum all cells U10L30
if Row 7 <= the current month
if Row 9 = "projected"
if C10:C30 = B31

I have tested all 4 arrays of the following formula individually and they
produce the correct value, but when I combine the 4 together as the following
I do not get the desired result:

{=SUMPRODUCT(--($U$7:$DL$7<=EOMONTH($B$1,0))--($U$9:$DL$9="Projected")*($U$10:$DL$30)*($C$10:$C$30=$B31))}

I've tried switching the order of the arrays and I get different results,
but never the correct result.

Thanks for reading.

 

[Eduardo]

Hi
try

=SUMPRODUCT(--($U$7:$DL$7<=EOMONTH($B$1,0)),--($U$9:$DL$9="Projected"),--($C$10:$C$30=$B31),($U$10:$DL$30))

 

[JoeU2004]

Assuming you copy-and-pasted the formula into your message -- which you
should always do, especially when you have a syntax question -- your mistake
is a missing comma before the second "--". There are many ways you can
write the SUMPRODUCT. For example:

=SUMPRODUCT(($U$7:$DL$7<=EOMONTH($B$1,0))*($U$9:$DL$9="Projected")*($U$10:$DL$30)*($C$10:$C$30=$B31))

(Does that formula really need to be entered as an array formula, as it
appears you had done?)

Note that you need "--" before conditional expressions only if they are not
used otherwise in an arithmetic expression. For example, the following is
equivalent:

=SUMPRODUCT(--($U$7:$DL$7<=EOMONTH($B$1,0)),--($U$9:$DL$9="Projected"),$U$10:$DL$30,--($C$10:$C$30=$B31))

Arguably that form (although I would reorder the last two terms; a personal
style preference) is more robust because it avoids #VALUE errors when some
of the cells in the range U10L30 are text.

To that end, I would write:

=SUMPRODUCT(($U$7:$DL$7<=EOMONTH($B$1,0))*($U$9:$DL$9="Projected")*($C$10:$C$30=$B31),$U$10:$DL$30)


----- original message -----

 

[T. Valko]

Try this:

=SUMPRODUCT(($U$7:$DL$7<=EOMONTH($B$1,0))*($U$9:$DL$9="Projected")*($C$10:$C$30=$B31)*$U$10:$DL$30)

 

[Sean Timmons]

Just a point of interest. SUMPRODUCT does not need to be entered with
CTRL+SHIFT+ENTER. Just enter as normal formula.

 

[ocuhcs]

All of the above comments were solutions - thanks for the insight.

 

[JoeU2004]

Errata....

I wrote 3 alternative formulations:

=SUMPRODUCT(($U$7:$DL$7<=EOMONTH($B$1,0))*($U$9:$DL$9="Projected")*($U$10:$DL$30)*($C$10:$C$30=$B31))
[....]
=SUMPRODUCT(--($U$7:$DL$7<=EOMONTH($B$1,0)),--($U$9:$DL$9="Projected"),$U$10:$DL$30,--($C$10:$C$30=$B31))
[....]
=SUMPRODUCT(($U$7:$DL$7<=EOMONTH($B$1,0))*($U$9:$DL$9="Projected")*($C$10:$C$30=$B31),$U$10:$DL$30)

Note that the second form does not work in this particular case because you
combine row and column ranges (UL and 10:30).

My intent in showing the second form was primarily to demonstrate when "--"
is needed before a conditional expression, namely when it is an individual
parameter.


----- original message -----