Announcement

Collapse
No announcement yet.

SUMIF Less than or Equal to a date

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • SUMIF Less than or Equal to a date



    Okay, I am trying to revise a formula, and I am sure I am somewhat close, but, I just cannot get this to work properly. The formula I have right now is:

    =SUMIF($E$7:$E$844,"<="&$A$1,$B$7:$B$844)

    Column E is a list of quantities, in number format.
    Column B is a list of dates, in date format (mm/dd/yy)
    Cell $A$1 contains a date I want to reference. This date may be occassionally changed manually, which is why I am referencing a specific cell within my formula.

    Based on the dates listed in Column E that meet the criteria of being less than or equal to the date listed in $A$1 I need to total the quantities listed in Column B.

    Thanks in advance for all your help!
    Sherry
    Orlando, FL

  • #2
    Re: SUMIF Less than or Equal to a date

    Now this formula works! It didn't work until after I rebooted my system! But at least it does work. And since I was so proud of myself, I added a 2nd formula in the cell below this one that calculates for a range of dates:

    =SUMPRODUCT(($E$7:$E$844>=$A$1)*($E$7:$E$844<=$A$2)*($B$7:$B$844))

    I am glad I was able to solve this one on my own. But that is only because I spend so much time reading everyone elses posts! Thanks again everyone. I figured I would post my additional formula on the off chance it can be helpful to someone else.
    Sherry
    Orlando, FL

    Comment


    • #3
      Re: SUMIF Less than or Equal to a date

      Well done, Always nice to solve your own problem.... and thanks for posting the additional example for others' benefit.
      Kind Regards, Will Riley

      LinkedIn: Will Riley

      Comment


      • #4
        Re: SUMIF Less than or Equal to a date

        Okay, I need help again. This formula has been working, and rather nicely. But now, over the last week, I am getting a #VALUE! error message, and I haven't a clue as to why. I have verified that ONLY numbers appear in column B, all formatted as numbers as well. I am at a loss as to why this working formula would stop working and result in an error. Any words of advice on resolving this woul;d be greatly appreiated! Thanks so very much!
        Sherry
        Orlando, FL

        Comment


        • #5
          Re: SUMIF Less than or Equal to a date

          I suppose the obvious question is what changed during the past week?

          Have you tried reducing the number of rows in the formula to see if the #Value persits. For example do this work

          =SUMPRODUCT(($E$7:$E$44>=$A$1)*($E$7:$E$44<=$A$2)*($B$7:$B$44))

          Cheers
          Andy

          Comment


          • #6


            Re: SUMIF Less than or Equal to a date

            Andy----

            Okay, I just plain don't get this. I used your "smaller" data formula, and it worked. Then I slowly increase the data range until it encompassed my entire data area, and now it works again. What is up with that? I am not sure what the problem was, but it is now working perfectly. I hail to OZ (as usual)! I would never have thought of reducing my data size within the formula. Andy, thanks so very much!!!!!!
            Sherry
            Orlando, FL

            Comment

            Working...
            X