+ Reply to Thread
Results 1 to 7 of 7

SUMIFS with time and date name ranges

  1. #1
    Registered User
    Join Date
    08-24-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    3

    SUMIFS with time and date name ranges

    Hello All,

    My first post here!!

    I am having issues getting SUMIFS function to work where the matching criteria are Date and Time.

    For example, Column A has bunch of dates (range name 'Date'), Column B has time (range name 'Time') and Column C has volume (range name 'Volume').

    If I use =SUMIFS(Volume,A:A,I2,B:B,J2) (where I2 and J2 hold the date and time I want to match), it works.

    But if I do =SUMIFS(Volume,Date,I2,Time,J2)

    or even =SUMIFS(Volume,A2:A100,I2,B2:B100,J2)

    I get 'Value' Error. Any suggestions what I am doing wrong?

    I was thinking that it was a formatting issue but then even first formula shouldnt work. Note that 'Date' and 'Time' are dynamically named ranges (using offset function).

    Note: This problem is only with SUMIFS. The other cousin i.e. SUMIF works fine with all.

    Thanks,

    Sumit

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

    Re: SUMIFS with time and date name ranges

    You get better help if you post an Excel-example of your workbook, without confidential information.
    Make sure the workbook demonstrates your desired results if possible, or just highlight the cells you're trying to fix.
    Use BEFORE/AFTER sheets if that helps make it clearer.
    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.

  3. #3
    Registered User
    Join Date
    08-24-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: SUMIFS with time and date name ranges

    Thanks Oeldere.

    Here are pictures of my problem. I isolated it as being a problem with dynamic name ranges.After.jpgBefore.jpg

  4. #4
    Forum Expert Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: SUMIFS with time and date name ranges

    The data ranges are inconsistent.

    The first formula works since 'Volume' refers to an entire column and so do A:A and B:B, while in the 2nd and 3rd column you have the sum tange as an entire column while the criteria ranges are not.
    Life's a spreadsheet, Excel!
    Say thanks, Click *

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

    Re: SUMIFS with time and date name ranges

    Quote Originally Posted by tradersumit View Post
    ... This problem is only with SUMIFS. The other cousin i.e. SUMIF works fine with all.
    SUMIF and SUMIFS essentially work the same way. There is always a 1 to 1 relationship between criteria range(s) and sum range, so the ranges that the formulas actually use are always the same size and shape. SUMIFS, though, forces you to explicity make them the same size (or you get an error as you discovered), SUMIF on the other hand, doesn't give you an error but it implicitly makes the sum range the same size as the criteria range, even if you don't specify it that way, so if you use this formula

    =SUMIF(A2:A100,I2,Volume)

    then that might not do what you think. As Volume is the whole of column C that means that you are actually doing this

    =SUMIF(A2:A100,I2,C1:C99)

    The sum range starts at the first cell of Volume and is the same size as the criteria range....so you might get incorrect results because if I2 matches A10 and A30 you'll actually get the sum of C9 and C29, not the values from the same rows
    Audere est facere

  6. #6
    Registered User
    Join Date
    08-24-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: SUMIFS with time and date name ranges

    That did it. I could have never figured it out. Thank a lot Ace_XL and daddylonglegs.

    Sumit

  7. #7
    Registered User
    Join Date
    02-10-2010
    Location
    Scotland
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: SUMIFS with time and date name ranges

    Hi

    I have a query of the same sort, but not sure if it will do what i want, but here goes. I have a spreasheed (see below), which has a number of columns but is currently running at over 2000 rows all with different sites, dates, quantities etc. What I am trying to achieve is a formula that if I filter the Date of Delivery to say August so that it captures 9 rows of info, that the bit at the bottom then returns the sum of the Qty Delivered, i.e. Site 1 would return a qty of 56,951. I have tried =Sumifs(I2:I9,E2:E9,"Site 1",D2:d9,"August") but it returns a - value.

    Anyone any idea where I'm going wrong?

    Thanks

    Chris

    Supplier Product Type Week No Date of delivery Site Order Number Cost per Litre Qty Ordered Qty Delivered
    01/08/2012 Site 1 22,000 21,948
    02/08/2012 Site 2 22,000 22,000
    03/08/2012 Site 3 22,000 21,999
    04/08/2012 Site 4 2,000 2,000
    05/08/2012 Site 5 35,000 35,000
    06/08/2012 Site 1 35,000 35,003
    07/08/2012 Site 2 35,000 35,000
    08/08/2012 Site 3 35,000 35,000
    208,000 207,950


    Site 1 -
    Site 2
    Site 3
    Site 4
    Site 5

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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