|
What's wrong with this formula?
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 |
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 -----Original Message----- 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 . |
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 -----Original Message----- 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 -----Original Message----- 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 . . |
On Fri, 4 Feb 2005 20:11:02 -0800, Ken M.
wrote: 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 |
On Fri, 4 Feb 2005 20:52:34 -0800, "Biff" wrote:
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 |
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" wrote: On Fri, 4 Feb 2005 20:11:02 -0800, Ken M. wrote: 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 |
On Fri, 4 Feb 2005 21:15:03 -0800, Ken M.
wrote: 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 |
| All times are GMT +1. The time now is 07:59 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com