Sumproduct Function Question

[mjones]

Hi All,

This function works:

=SUMPRODUCT(AND(($H$9<>"x"),($C$9+30<TODAY()))*($D$9=D77),$E$9)

But when I update it to this one, it gives a $0 result and there
should be a number.

=SUMPRODUCT(AND(($H$6:$H$66<>"x"),($C$6:$C$66+30<TODAY()))*($D$6:$D
$66=D77),$E$6:$E$66)

or

{=SUMPRODUCT(AND(($H$6:$H$66<>"x"),($C$6:$C$66+30<TODAY()))*($D$6:$D
$66=D77),$E$6:$E$66)}

Any ideas would be much appreciated?

Michele

 

[Guest]

And() and Or() don't like arrays much. From what I can tell of the formula,
you're trying to sum column E when the following criteria are met:

column H is not equal to "x"
column c + 30 is less than today's date
column d = the value in d77

If that's the case, it should work without the AND function like so:

=sumproduct(--($H$6:$H$66<>"x"),--($C$6:$C$66+30<today()),--($D$6:$D$66=d77),$E$6:$E$66)

If I misinterpreted anything in there, please let me know.

 

[mjones]

Hi,

I tried it as you suggested:

{=SUMPRODUCT(($H$6:$H$66<>"x"),($C$6:$C$66+30<TODAY()),($D$6:$D
$66=D77),$E$6:$E$66)}

I'm not sure why you put in the dashes. But it still comes out as
zero.

Your understanding of what I'm trying to accomplish is accurate.

Maybe it's to do with the cell formats. They are:

C - 1/18/2007 formated as Date 18-Jan
D - general for company names
E - custom - _(_($* #,##0.00_)_);_(_($* (#,##0.00)_);_(_($* "-"??
_)_);_(_(@_)_)
G - custom - _(_($* #,##0.00_)_);_(_($* (#,##0.00)_);_(_(* ""??
_)_);_(_(@_)_) - this is where the formula is
H - general - this is where the x will be to indicate paid or blank if
not paid

Any other ideas would be greatly appreciated.

Thanks,

Michele
-----------

 

[Roger Govier]

Hi

It is not an array entered formula so you don't need to enter with
Control+Shift+Enter. Just Enter will do.
There will not be any curly braces { } around the formula.

You do need the double unary minus to coerce the tests of True or False
to 1 or 0, if you are going to uses a comma between the expressions.
Your test for dates will not work as you have expressed it, turn it
round as shown in the formula below to take the 30 days away from today,
rather than trying to add it to the dates in C6:C66

=SUMPRODUCT(--($H$6:$H$66<>"x"),--($C$6:$C$66<TODAY()-30),
--($D$6:$D$66=D77),$E$6:$E$66)

Alternatively, without the double unary minus
=SUMPRODUCT(($H$6:$H$66<>"x")*($C$6:$C$66<TODAY()-30)*
($D$6:$D$66=D77)*$E$6:$E$66)

In this case the multiplication between expressions does the coercion
for True and False to 1 and 0
--
Regards

Roger Govier


Hi,

I tried it as you suggested:

{=SUMPRODUCT(($H$6:$H$66<>"x"),($C$6:$C$66+30<TODAY()),($D$6:$D
$66=D77),$E$6:$E$66)}

I'm not sure why you put in the dashes. But it still comes out as
zero.

Your understanding of what I'm trying to accomplish is accurate.

Maybe it's to do with the cell formats. They are:

C - 1/18/2007 formated as Date 18-Jan
D - general for company names
E - custom - _(_($* #,##0.00_)_);_(_($* (#,##0.00)_);_(_($* "-"??
_)_);_(_(@_)_)
G - custom - _(_($* #,##0.00_)_);_(_($* (#,##0.00)_);_(_(* ""??
_)_);_(_(@_)_) - this is where the formula is
H - general - this is where the x will be to indicate paid or blank if
not paid

Any other ideas would be greatly appreciated.

Thanks,

Michele
-----------

 

[mjones]

Great, that worked. Your really smart. Thanks.

----------