Help Debug Complex Formula (SUMPRODUCT? SUMIF?)

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

David Lipetz - 12 Sep 2006 18:09 GMT

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

Reply to this Message

glenton (glenton@leviqqio.com - 12 Sep 2006 18:27 GMT

=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

> Folks,
>
[quoted text clipped - 22 lines]
> Thanks,
> David

Reply to this Message

David Lipetz - 12 Sep 2006 18:59 GMT

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

> =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.
[quoted text clipped - 39 lines]
>> Thanks,
>> David

Reply to this Message

Bob Phillips - 12 Sep 2006 19:16 GMT

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.

Signature

HTH

Bob Phillips

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

> Glenton,
>
[quoted text clipped - 35 lines]
> >> 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
[quoted text clipped - 4 lines]
> >> Thanks,
> >> David

Reply to this Message