+ Reply to Thread
Results 1 to 7 of 7

What's wrong with this formula?

  1. #1
    Ken M.
    Guest

    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


  2. #2
    Biff
    Guest

    What's wrong with this formula?

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


  3. #3
    Biff
    Guest

    What's wrong with this formula?

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

    >.
    >


  4. #4
    Ron Rosenfeld
    Guest

    Re: What's wrong with this formula?

    On Fri, 4 Feb 2005 20:11:02 -0800, Ken M. <KenM@discussions.microsoft.com>
    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

  5. #5
    Ron Rosenfeld
    Guest

    Re: What's wrong with this formula?

    On Fri, 4 Feb 2005 20:52:34 -0800, "Biff" <biffinpitt@comcast.net> 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

  6. #6
    Ken M.
    Guest

    Re: What's wrong with this formula?

    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. <KenM@discussions.microsoft.com>
    > 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
    >


  7. #7
    Ron Rosenfeld
    Guest

    Re: What's wrong with this formula?

    On Fri, 4 Feb 2005 21:15:03 -0800, Ken M. <KenM@discussions.microsoft.com>
    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1