Help Debug Complex Formula (SUMPRODUCT? SUMIF?)

[David Lipetz]

Folks,

I'm really struggling with this one.

I've got 354 rows of data (rows 3:352). In column G there may be a date. In
column M there is a number (1-12) which represents a monthly period, and in
column O there is a dollar amount which represents a montly invoice total.

I need to construct a formula which calculates the sum of O for a specific
period M where there is a date entered (non-blank cell) in G.

The formula below is what I constructed but it does not work. Rather it
calculates the sum of O for the specified period in M but then multiplies
the sum ($16,200) by the number of non-blank cells found in G (3) for a a
result of $48,600.

{=SUMPRODUCT(--(Master!$G$3:$G$352<>0)*SUMIF(Master!$M$3:$M$352,"3",Master!$O$3:$O$352))}

An example of what I want it to do:
For all rows where M = 3 and G is non-blank
Sum value of O

Your assistance would be greatly appreciated.

Thanks,
David

 

[Guest]

=sumproduct(--($g$3:$g$352<>0),--($m$3:$m$352=3),$o$3:$o352)
would surely do it. No need for array formulas on this one.

But if you're really keen on them
{=sum(if($g$3:$g$352<>0,if($m$3:$m$352=3,$0$3:$o352)))}
would do it. (note for readers not familiar with array formulas, enter
above without curly braces and hit shift+ctrl+enter instead of enter)

Regards
Glenton
www.leviqqio.com

 

[David Lipetz]

Glenton,

Thank you VERY much. Works like a charm. Can you step me through the logic
of your first example? Not sure I understand why it works.

David

"glenton ([email protected]"

 

[Bob Phillips]

Builds an array of TRUE/FALSE values in $g$3:$g$352 that do not equal 0

Another array of TRUE/FALSE values in $m$3:$m$352 equals 3

and an array of values in $o$3:$o352

which SP multiplies all corresponding elements together and sums them.


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)