Sumproduct syntax question
 

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.

Sumproduct syntax question
 

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))

"ocuhcs" wrote:

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.

Sumproduct syntax question
 

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:$D L$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:$D L$9="Projected")*($C$10:$C$30=$B31),$U$10:$DL$30)


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

"ocuhcs" wrote in message
...
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.

Sumproduct syntax question
 

Try this:

=SUMPRODUCT(($U$7:$DL$7<=EOMONTH($B$1,0))*($U$9:$D L$9="Projected")*($C$10:$C$30=$B31)*$U$10:$DL$30)
--
Biff
Microsoft Excel MVP


"ocuhcs" wrote in message
...
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.

Sumproduct syntax question
 

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

"ocuhcs" wrote:

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.

Sumproduct syntax question
 

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

"Sean Timmons" wrote:

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

"ocuhcs" wrote:

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.

Sumproduct syntax question
 

Errata....

I wrote 3 alternative formulations:
=SUMPRODUCT(($U$7:$DL$7<=EOMONTH($B$1,0))*($U$9:$D L$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:$D L$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 (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 -----

"JoeU2004" wrote in message
...
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:$D L$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:$D L$9="Projected")*($C$10:$C$30=$B31),$U$10:$DL$30)


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

"ocuhcs" wrote in message
...
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.