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<T ODAY()))*($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

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.

--
Mike Lee
McKinney,TX USA


"mjones" wrote:

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<T ODAY()))*($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

Hi,

I tried it as you suggested:

{=SUMPRODUCT(($H$6:$H$66<"x"),($C$6:$C$66+30<TODA Y()),($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 a

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

On Mar 14, 4:57 pm, mikelee101 <mikelee101athotmaildotcom wrote:
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.

--
Mike Lee
McKinney,TX USA



"mjones" wrote:
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<T ODAY()))*($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- Hide quoted text -

- Show quoted text -

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


"mjones" wrote in message
ups.com...
Hi,

I tried it as you suggested:

{=SUMPRODUCT(($H$6:$H$66<"x"),($C$6:$C$66+30<TODA Y()),($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 a

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

On Mar 14, 4:57 pm, mikelee101 <mikelee101athotmaildotcom wrote:
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.

--
Mike Lee
McKinney,TX USA



"mjones" wrote:
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<T ODAY()))*($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- Hide quoted text -

- Show quoted text -

Great, that worked. Your really smart. Thanks.

----------
On Mar 14, 6:10 pm, "Roger Govier"
wrote:
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

"mjones" wrote in message

ups.com...
Hi,

I tried it as you suggested:

{=SUMPRODUCT(($H$6:$H$66<"x"),($C$6:$C$66+30<TODA Y()),($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 a

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

On Mar 14, 4:57 pm, mikelee101 <mikelee101athotmaildotcom wrote:



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.

--
Mike Lee
McKinney,TX USA

"mjones" wrote:
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<T ODAY()))*($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- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -