+ Reply to Thread
Results 1 to 12 of 12

SumIfs containing a greater than or equal to And less than or equal to

  1. #1
    Registered User
    Join Date
    11-26-2013
    Location
    Jackson, Wyoming
    MS-Off Ver
    Excel 2010
    Posts
    29

    SumIfs containing a greater than or equal to And less than or equal to

    I'm trying to create a formula that can sum a range of data based on multiple conditions and be dynamic to change the time period criteria because I'm using this to calculate month-to-date information, which I will need to change periodically. I need a formula that I can easily change certain criteria because this will be a very large report.

    I have the following formula:

    =SUMIFS('[Weekly Report Data Drop.xlsm]Actuals'!$H$4:$H$59793,'[Weekly Report Data Drop.xlsm]Actuals'!$E$4:$E$59793,$A$6,'[Weekly Report Data Drop.xlsm]Actuals'!$D$4:$D$59793,$A$7,'[Weekly Report Data Drop.xlsm]Actuals'!$C$4:$C$59793,$A2,'[Weekly Report Data Drop.xlsm]Actuals'!$M$3:$M$59793,">="&T2,'[Weekly Report Data Drop.xlsm]Actuals'!$M$3:$M$59793,"<="&T3)

    The last two conditions with the >= and <= seem to be causing the formula to error out, the working was working before I ended those last

    I can't get a sample to a small enough file size to attached, but cell T2 and T3 contain dates so 8/1/2014 in T2 and 8/16/2014 T3

  2. #2
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: SumIfs containing a greater than or equal to And less than or equal to

    I can't see an obvious problem with your formula - you say you get an error, which error?

    Is the source workbook open (Weekly Report Data Drop.xlsm)? SUMIFS won't work with closed source workbooks (you get #VALUE! error)
    Audere est facere

  3. #3
    Registered User
    Join Date
    11-26-2013
    Location
    Jackson, Wyoming
    MS-Off Ver
    Excel 2010
    Posts
    29

    Re: SumIfs containing a greater than or equal to And less than or equal to

    the source workbook is open and I checked to make sure the source was correct. I'm getting a #VALUE error. could this be due to formatting?

  4. #4
    Registered User
    Join Date
    11-26-2013
    Location
    Jackson, Wyoming
    MS-Off Ver
    Excel 2010
    Posts
    29

    Re: SumIfs containing a greater than or equal to And less than or equal to

    I was able to condense the file enough to create a sample file, Sheet 2 contains the report and where the formula is located, sheet 2 contain a sample of the data I'm pulling from, which is my live version is in another workbook
    Attached Files Attached Files

  5. #5
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: SumIfs containing a greater than or equal to And less than or equal to

    why do you use differant workbooks.

    if you are able to use 1 workbook for that, your problem will be gone.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  6. #6
    Registered User
    Join Date
    11-26-2013
    Location
    Jackson, Wyoming
    MS-Off Ver
    Excel 2010
    Posts
    29

    Re: SumIfs containing a greater than or equal to And less than or equal to

    I need different workbooks because I need to send out the report and if the data section was contained in the report I file would to be large. I will be having 4 sheets of data on with over 40,000 rows in each.

    But the different workbooks isn't causing the problem because I have been using the same formula without the >= and <= criteria and it looks

  7. #7
    Registered User
    Join Date
    04-01-2014
    Location
    Houston, Texas
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: SumIfs containing a greater than or equal to And less than or equal to

    I'm sorry, but I can not figure out what column M have to do with the dates in T2 and T3? Did I miss something? If it's not that, then check the year in your end date.
    Last edited by MechLoco; 04-15-2014 at 03:03 PM.

  8. #8
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: SumIfs containing a greater than or equal to And less than or equal to

    All the ranges need to be of equal size..

    =SUMIFS('[Weekly Report Data Drop.xlsm]Actuals'!$H$4:$H$59793,'[Weekly Report Data Drop.xlsm]Actuals'!$E$4:$E$59793,$A$6,'[Weekly Report Data Drop.xlsm]Actuals'!$D$4:$D$59793,$A$7,'[Weekly Report Data Drop.xlsm]Actuals'!$C$4:$C$59793,$A2,'[Weekly Report Data Drop.xlsm]Actuals'!$M$3:$M$59793,">="&T2,'[Weekly Report Data Drop.xlsm]Actuals'!$M$3:$M$59793,"<="&T3)

  9. #9
    Registered User
    Join Date
    11-26-2013
    Location
    Jackson, Wyoming
    MS-Off Ver
    Excel 2010
    Posts
    29

    Re: SumIfs containing a greater than or equal to And less than or equal to

    Thanks you very much for the help, the year was the issue and making sure the range was the same for all the cells fixed the problem.

  10. #10
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: SumIfs containing a greater than or equal to And less than or equal to

    Glad to help, thanks for the feedback.

  11. #11
    Registered User
    Join Date
    04-01-2014
    Location
    Houston, Texas
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: SumIfs containing a greater than or equal to And less than or equal to

    Quote Originally Posted by teton88 View Post
    Thanks you very much for the help, the year was the issue and making sure the range was the same for all the cells fixed the problem.

    I am glad everything is working.

  12. #12
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: SumIfs containing a greater than or equal to And less than or equal to

    If you use SUMPRODUCT instead of SUMIFS then the source file will not have to be open.

    =SUMPRODUCT('[Weekly Report Data Drop.xlsm]Actuals'!$H$4:$H$59793,--('[Weekly Report Data Drop.xlsm]Actuals'!$E$4:$E$59793=$A$6),--('[Weekly Report Data Drop.xlsm]Actuals'!$D$4:$D$59793=$A$7),--('[Weekly Report Data Drop.xlsm]Actuals'!$C$4:$C$59793=$A2),--('[Weekly Report Data Drop.xlsm]Actuals'!$M$4:$M$59793>=T2),--('[Weekly Report Data Drop.xlsm]Actuals'!$M$4:$M$59793<=T3))
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] COUNTIFS function w/greater than or equal to, and less than or equal to time values
    By AliciaRenee in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 03-27-2014, 03:57 PM
  2. [SOLVED] SUMIFS greater than or equal
    By telton in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-11-2013, 04:55 AM
  3. Greater than or equal
    By SuncrestInsurance in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-18-2011, 07:52 PM
  4. greater equal less than
    By ianm34 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-06-2010, 12:17 PM
  5. equal to and greater than
    By Saz in forum Excel General
    Replies: 5
    Last Post: 08-30-2006, 07:02 AM

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