Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 6 of 6

Thread: SUMIF Less than or Equal to a date

  1. #1
    Join Date
    10th September 2003
    Location
    Orlando, FL
    Posts
    129

    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. #2
    Join Date
    10th September 2003
    Location
    Orlando, FL
    Posts
    129

    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

  3. #3
    Join Date
    28th January 2003
    Location
    Solihull - England
    Posts
    8,678

    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

    Web Presence:
    LinkedIn: Will Riley

  4. #4
    Join Date
    10th September 2003
    Location
    Orlando, FL
    Posts
    129

    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

  5. #5
    Join Date
    7th March 2003
    Location
    Essex, England
    Posts
    11,310

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

    Cheers
    Andy


  6. #6
    Join Date
    10th September 2003
    Location
    Orlando, FL
    Posts
    129

    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

Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Replies: 4
    Last Post: April 4th, 2008, 11:15
  2. Values Which Are Equal Not Treated Equal
    By hollydog in forum EXCEL HELP
    Replies: 2
    Last Post: July 6th, 2007, 02:08
  3. Replies: 3
    Last Post: March 8th, 2007, 03:16
  4. Sumif Between Date Criteria
    By Katyco in forum EXCEL HELP
    Replies: 18
    Last Post: December 14th, 2006, 12:01
  5. SUMIF based on date ranges
    By safaller in forum EXCEL HELP
    Replies: 1
    Last Post: May 9th, 2006, 01:53

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
  •  
porno