What's wrong with this formula?

[Ken M.]

This is my combo of Bob and Aladin. Yet still not working. Where is my error?

=SUMIF(--(J2:J250>=TODAY()+29)&I2:I250<=.01)

Walking through this in baby steps...

In cell T3 I am asking Excel to... Sum if J2-J250 is Today or plus the next
29 days and I20-I250 has a dollar amount greater than or equal to a penny.

Is that not what I am doing? REALLY STUCK GUYS! Thanks for the continued
help.

Ken

 

[Biff]

Hi!

Try this:

=SUMPRODUCT(--(J2:J250>=TODAY()+29),--(I2:I250<=.01))

This will COUNT the number of times where both conditions
are true. If you want a SUM, you need another argument.

Biff

 

[Biff]

Hold on there a second!

Ooops!

I just noticed this:

and I20-I250 has a dollar amount greater than or equal to
a penny.

So change the formula to:

=SUMPRODUCT(--(J2:J250>=TODAY()+29),--(I2:I250>=.01))

Biff

 

[Ron Rosenfeld]

This is my combo of Bob and Aladin. Yet still not working. Where is my error?

=SUMIF(--(J2:J250>=TODAY()+29)&I2:I250<=.01)

Walking through this in baby steps...

In cell T3 I am asking Excel to... Sum if J2-J250 is Today or plus the next
29 days and I20-I250 has a dollar amount greater than or equal to a penny.

Is that not what I am doing?

Nope.

The SUMIF function does not do multiple comparisons.

You've got your comparison operators backwards.
You are using a text concatenation operator (&) which doesn't do what you want
in a mathematical expression.

The double unary is unnecessary.

And you need some parenthesis to make the comparisons do what you want.

Try:

=SUMPRODUCT((J2:J250<=(TODAY()+29))*(I2:I250>=0.01)*I2:I250)


--ron

 

[Ron Rosenfeld]

o change the formula to:

=SUMPRODUCT(--(J2:J250>=TODAY()+29),--(I2:I250>=.01))

Ignore my message. I see you addressed SUM vs COUNT in your previous post.
But I think the OP wanted a SUM; perhaps not.


--ron

 

[Ken M.]

Thanks Ron. The formula works PERFECTLY. I just input a huge test group of
easy number and double checked manually. After 30 days it excludes perfectly.
And give us a clear 30 days window on cash flow.

 

[Ron Rosenfeld]

Thanks Ron. The formula works PERFECTLY. I just input a huge test group of
easy number and double checked manually. After 30 days it excludes perfectly.
And give us a clear 30 days window on cash flow.

You're welcome. Glad to help and thank you for the feedback.


--ron