Sumproduct syntax question

  • Thread starter Thread starter ocuhcs
  • Start date Start date
O

ocuhcs

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

My goal is to:
Sum all cells U10:DL30
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.
 
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))
 
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 U10:DL30 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 -----
 
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)
 
Just a point of interest. SUMPRODUCT does not need to be entered with
CTRL+SHIFT+ENTER. Just enter as normal formula.
 
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)
Click to expand...

Note that the second form does not work in this particular case because you
combine row and column ranges (U:DL 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 -----
 
Back
Top