Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

# Thread: SUMIF Less than or Equal to a date

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

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.

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.

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!

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
VB:
```=SUMPRODUCT((\$E\$7:\$E\$44>=\$A\$1)*(\$E\$7:\$E\$44<=\$A\$2)*(\$B\$7:\$B\$44))

```

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

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

#### Posting Permissions

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