+ Reply to Thread
Results 1 to 11 of 11

SUMIF with partial date match in range column

  1. 07-16-2019, 05:44 PM #1
    rt2canoe
    rt2canoe is offline
    Registered User
    Join Date
    07-16-2019
    Location
    Maine
    MS-Off Ver
    Office 365
    Posts
    5

    SUMIF with partial date match in range column

    When I export sales data from my POS system, the date and time of the transaction are combined into one column. I need to sum all the individual sales on a given date, but because of the addition of the time stamp, my SUMIF formula won't work. The date/time data looks like this:

    Column B
    7/13/2019 16:13
    7/13/2019 16:02
    7/13/2019 15:48
    7/13/2019 15:35
    7/13/2019 15:13


    To sum all sales that took place on 7/13/19 I tried the following:

    =SUMIF(B:B,Q8,H:H)

    The dates and times are combined in column B.
    The criteria cell is Q8 (which contains the date 7/13/2019)
    The sales numbers are in column H

    How can I get the formula to look past the time stamps and sum all the 7/13/2019 transactions?

    Thanks!
    Reply With QuoteRegister To Reply
  2. 07-16-2019, 05:54 PM #2
    MrShorty
    MrShorty is online now
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP, 2007, 2024
    Posts
    16,437

    Re: SUMIF with partial date match in range column

    I'm sure there are a few different ways to do it. Recognizing that the 7/13/19 value in Q8 likely represents the date/time value of 7/13/19 0:00:00, and that all of my desired sum values are between midnight on 7/13 and midnight on 7/14, a SUMIFS() like =SUMIFS(H:H,B:B,">="&Q8,B:B,"<"&Q8+1) should capture all of the values with a time stamp any time during the day of 7/13.

    Edit to add: I'm also assuming that all of the date/time stamps are being recognized as actual date/time serial numbers and are not text.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.
    Reply With QuoteRegister To Reply
  3. 07-17-2019, 10:43 AM #3
    rt2canoe
    rt2canoe is offline
    Registered User
    Join Date
    07-16-2019
    Location
    Maine
    MS-Off Ver
    Office 365
    Posts
    5

    Re: SUMIF with partial date match in range column

    Thanks for your help on this. The new formula is still returning a 0 value. I see that my date and time column appears formatted in the cell as 7/13/2019 16:13, but the data in the formula bar reads 7/13/2019 4:13:13 PM. Not sure if that has to do with your additional point about serial numbers vs text.
    Reply With QuoteRegister To Reply
  4. 07-17-2019, 10:57 AM #4
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2504 (Windows 11 Home 24H2 64-bit)
    Posts
    90,995

    Re: SUMIF with partial date match in range column

    Maybe attach the workbook for troubleshooting.
    Ali

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    NB:     as a Moderator, I never accept friendship requests.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  5. 07-17-2019, 02:52 PM #5
    CARACALLA
    CARACALLA is offline
    Forum Expert
    Join Date
    09-25-2015
    Location
    Milan Italy
    MS-Off Ver
    office 365
    Posts
    1,998

    Re: SUMIF with partial date match in range column

    Try

    =SUMPRODUCT((INT(B1:B100)=Q8)*(H1:H100))
    Reply With QuoteRegister To Reply
  6. 07-18-2019, 12:52 PM #6
    rt2canoe
    rt2canoe is offline
    Registered User
    Join Date
    07-16-2019
    Location
    Maine
    MS-Off Ver
    Office 365
    Posts
    5

    Re: SUMIF with partial date match in range column

    I got a #VALUE! result. This one is beyond my knowledge, so I'm not sure how to tweak it.
    Reply With QuoteRegister To Reply
  7. 07-18-2019, 12:55 PM #7
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2504 (Windows 11 Home 24H2 64-bit)
    Posts
    90,995

    Re: SUMIF with partial date match in range column

    Will you please attach a small sample Excel workbook?

    Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.
    Reply With QuoteRegister To Reply
  8. 07-18-2019, 01:09 PM #8
    rt2canoe
    rt2canoe is offline
    Registered User
    Join Date
    07-16-2019
    Location
    Maine
    MS-Off Ver
    Office 365
    Posts
    5

    Re: SUMIF with partial date match in range column

    Thanks for the instructions. I had been trying to figure out what I was doing wrong. I've attached a sample sales report. To the side you'll where I need to calculate total sales by date. Thanks!
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  9. 07-18-2019, 01:15 PM #9
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2504 (Windows 11 Home 24H2 64-bit)
    Posts
    90,995

    Re: SUMIF with partial date match in range column

    Try this:

    =SUMIFS($H$2:$H$20,$B$2:$B$20,">="&K2,$B$2:$B$20,"<"&K2+1)
    Reply With QuoteRegister To Reply
  10. 07-18-2019, 01:21 PM #10
    CARACALLA
    CARACALLA is offline
    Forum Expert
    Join Date
    09-25-2015
    Location
    Milan Italy
    MS-Off Ver
    office 365
    Posts
    1,998

    Re: SUMIF with partial date match in range column

    A
    B
    C
    D
    E
    F
    G
    H
    I
    J
    K
    L
    1
    		 Sale# Date Customer Cashier Sub total Shipping Tax Total Total Sales by Date
    2
    785
    7/13/2019 14:33
    119.42
    6.56
    125.98
    7/11/2019
    285.62
    3
    784
    7/13/2019 13:54
    15.16
    0.83
    15.99
    7/12/2019
    162.96
    4
    783
    7/13/2019 13:51
    174.27
    9.58
    183.85
    7/13/2019
    582.09
    5
    782
    7/13/2019 13:37
    16.07
    0.88
    16.95
    6
    781
    7/13/2019 13:34
    24.95
    1.37
    26.32
    7
    780
    7/13/2019 13:09
    94.39
    5.19
    99.58
    8
    779
    7/13/2019 12:17
    42.95
    2.36
    45.31
    9
    778
    7/13/2019 11:29
    55.16
    3.03
    58.19
    10
    777
    7/13/2019 10:43
    9.4
    0.52
    9.92
    11
    776
    7/12/2019 15:39
    48.86
    2.69
    51.55
    12
    775
    7/12/2019 15:26
    61.54
    3.39
    64.93
    13
    774
    7/12/2019 15:25
    44.06
    2.42
    46.48
    14
    773
    7/11/2019 16:48
    5.4
    0.3
    5.7
    15
    772
    7/11/2019 13:26
    15.16
    0.83
    15.99
    16
    771
    7/11/2019 12:39
    90.3
    4.97
    95.27
    17
    770
    7/11/2019 12:39
    22.95
    1.26
    24.21
    18
    769
    7/11/2019 12:32
    19.57
    1.08
    20.65
    19
    768
    7/11/2019 12:23
    22.4
    1.23
    23.63
    20
    767
    7/11/2019 12:21
    94.95
    5.22
    100.17


    L2=IF($K2<>"",SUMPRODUCT((INT($B$2:$B$100)=$K2)*($H$2:$H$100)),"") copy down
    Reply With QuoteRegister To Reply
  11. 07-18-2019, 03:37 PM #11
    rt2canoe
    rt2canoe is offline
    Registered User
    Join Date
    07-16-2019
    Location
    Maine
    MS-Off Ver
    Office 365
    Posts
    5

    Re: SUMIF with partial date match in range column

    Both of those last two suggestions worked beautifully. I really appreciate the help!
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Compare Date both Full match/ partial match between two columns
    By cyboincomp in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 11-21-2018, 03:02 AM
  2. Repeating SUMIF Non Continuous Range + Column Match
    By Thedude1234 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-31-2018, 05:54 PM
  3. [SOLVED] SUMIF / AVERAGEIF using MATCH for column range
    By terratushi in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-26-2018, 08:57 AM
  4. [SOLVED] index match with sumif date range
    By namluke in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-09-2017, 09:45 AM
  5. STUCK** SUMIF INDEX MATCH - formula output sum of date range or month sum
    By Antprod in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-05-2015, 08:13 AM
  6. SUMIF with only a partial match
    By DON-E in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 09-06-2013, 04:58 PM
  7. sumif based on partial match
    By gregangelagrace@gmail.com in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-03-2006, 08:20 PM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1