Comments on: Sum if between two dates in Excel: SUMIFS with date range as criteria

Working on a report, investment plan or any other dataset with dates, you may often need to sum values within a specific period. This tutorial will teach you a quick and easy solution - SUMIFS formula with date range as criteria. Continue reading →

Comments page 2. Total comments: 210

  1. Hi,

    I have 2 separate worksheets, one with a list of item codes and what stock I am holding. The other inbound deliveries (as below). As i need a view of current stock and inbound volume to see how many days coverage I will have, what formula would I use to look up the item code from the stock sheet and calculate total volume ordered on the inbound deliveries but ignore the volume if beyond the delivery date (it will be a continuous list which will include future inbounds).

    Supplier Item Item Description Order Date Qty Ordered Delivery Date Received
    ABC 123 Socks Size 11 23/04/2023 10 28/04/2023 10
    DEF 456 Socks Size 12 24/04/2023 10 27/04/2023 0
    DEF 456 Socks Size 12 25/04/2023 10 27/04/2023 10
    ABC 789 Socks Size 13 26/04/2023 10 28/04/2023 10
    DEF 987 Socks Size 14 27/04/2023 10 29/04/2023 0
    ABC 123 Socks Size 11 28/04/2023 10 30/04/2023 10

    Thanks

  2. I have a Start Date and End Date for the activities along with the required manpower. Then I want to use the SUMIF function to add the manpower within two separate dates (i.e. Week starting Dates) that link with the Start and End Date of the Activity.

    Start Finish Man Power Reqd.
    22-Mar-24 18-Apr-24 1
    17-May-24 28-Jun-24 3
    24-Jun-24 5-Aug-24 3
    16-Jul-24 26-Aug-24 3
    16-Apr-24 29-Apr-24 1
    27-May-24 7-Jun-24 1
    17-Sep-24 30-Sep-24 1
    1-Oct-24 14-Oct-24 1
    16-Oct-24 29-Oct-24 1
    8-Jan-25 14-Jan-25 3

    Week Starting Dates Manpower Requirement
    3/18/2024 ?
    3/25/2024 ?
    4/1/2024
    4/8/2024
    4/15/2024
    4/22/2024
    4/29/2024
    5/6/2024
    5/13/2024
    5/20/2024
    5/27/2024
    6/3/2024
    6/10/2024
    6/17/2024
    6/24/2024
    7/1/2024
    7/8/2024
    7/15/2024
    7/22/2024
    7/29/2024
    8/5/2024
    8/12/2024
    8/19/2024
    8/26/2024
    9/2/2024
    9/9/2024
    9/16/2024
    9/23/2024
    9/30/2024

    Can someone please help me with this?

    1. Hi!
      I hope you have studied the recommendations in the tutorial above. It contains answers to your question.
      Formula example:

      =SUMIFS(C1:C10,A1:A10,"<="&$D$1,B1:B10,">="&$D$1)

      1. Thank you so much!

  3. This code is calculating only if the date like that 01/04/2023 02/04/2023 but if I want to get data for today only and both dates are on the same day still gives me all the data

    =SUMIFS(T_Re_Sales[Qty],T_Re_Sales[Products],[@[Item Name]],T_Re_Sales[Date],">="&$F$8,T_Re_Sales[Date],"<="&EOMONTH($H$8,0))

    1. Hi!
      It is very difficult to understand a formula that contains unique references to your workbook worksheets. Hence, I cannot check its work. Note, however, that the EOMONTH function always returns the last date of the month. For your data it is 30/04/2023.

  4. I'm hoping this thread is the correct one based on my SUMIFS question.

    I'm using this formula =SUMIFS(H19:H2261,F19:F2261,">="&A4,F19:F2261,"<="&B4)

    H19:H2261 represents dollar amounts for Selling Fees
    F19:F2261 represents a category for a product
    A4 and B4 Represent a predetermined date range where A4 is the 1st day of the month and B4 is the last day of the month

    The formula currently works just fine, my question is, how do I go about adding a second column of dollar amounts to the formula, let's say Payment Processing Fees? This additional column would be represented as J19:J2261 for example.

    In short, I'd like this formula to also add the dollar amounts of an additional column of fees still within the same parameters of the formula listed above. I've tried several variations to no avail. Any assistance would be greatly appreciated!

    1. Hello!
      SUMIFS function can calculate the conditional sum in only one range. Add another SUMIFS formula for the second range.

      =SUMIFS(H19:H2261,F19:F2261,">="&A4,F19:F2261,"<="&B4) + SUMIFS(J19:J2261,F19:F2261,">="&A4,F19:F2261,"<="&B4)

      I hope I answered your question.

      1. Yes, that did it!!! Thank you so very much, I wish I had known you could place more than one SUMIFS statement on one line. Thank you so very much!!!

  5. Hi!
    I'm trying to calculate sort of the inverse. I have a table of open orders with a booking date, payment date and the total cost per order. I want to calculate the cumulative of all open orders in each given date.

    Example order table:

    Order N Order Date Payment Made Cost
    1 01/01/2023 02/01/2023 1
    2 01/01/2023 03/01/2023 4
    3 02/01/2023 03/01/2023 2
    4 03/01/2023 04/01/2023 3

    What I need:
    Date Open Order Value
    01/01/2023 5
    02/01/2023 6
    03/01/2023 3
    04/01/2023 0

    Anyone know how I can SUM orders which include the given date within their range, instead of the inverse?

    Thanks!

    1. Figured it out.
      I ended up using:
      =SUMIFS($E$1:$E$3871,$C$1:$C$3871,""&J2)
      Where the first range is the total cost I wanted to calculate and then there is the first date filter and the second based on J2, which is the date in which I want to see the total cost accumulated.

    2. Hi!
      All the necessary information is in the article above.

      =SUMIFS($D$1:$D$4,$B$1:$B$4,"<="&B1,$C$1:$C$4,">"&B1)

      1. Ah right, Thank you! Did not see your comment.

  6. I'm trying to sum spending in the most current week starting from Sunday. Column A contains the amounts spent, Column B contains the exact time.
    I can sum the spending in the last 7 days with sumif(B2:B10000,">"&NOW()-7,A2:A10000). However, when I replace &NOW()-7 by &TODAY()-WEEKDAY(&TODAY(),2) I get a Formula Parse Error. What's the correct way of doing this?

    1. Hi!
      This formula works for me. But I don't see your data.

      =SUMIF(B2:B10000,">"&TODAY()-WEEKDAY(TODAY(),2),A2:A10000)

  7. I am trying to figure out a simpler way to see if a date range has any dates between a set date range without writing out a long and complicated formula.

    Example:
    Set date range : 1/1/2023 - 2/28/2023
    Date range checking: 2/1/2023 - 3/30/2023

    The set date range will change monthly. It is to verify if I have any promos within a date range.

      1. Yes, Kimberly! I'm working with hotel room block budgets and multiple room rates based on specific nights within a larger date range and struggling to find a solution. COUNTIF and SUMIF will only count in single increments. I'm trying to calculate counting the total nights within a variety of date price ranges based on a guests' TOTAL room stay length.

        For example:
        Column A (Guest Name)
        Column B (Check In Date): 1/30/2024
        Column C (Check Out Date): 2/14/2024

        However, 1/30-2/7 is $200/night, 2/8-2/11 is $500/night, and 2/12-2/14 is $300/night. So what formula would return total nights within each of the 3 mini date ranges, based on the OVERALL date range?

        1. Hi! You can write dates and costs in 2 separate columns. For example:
          1/30/2023 200
          2/1/2023 200
          .....
          2/8/2023 500
          and so on.
          To calculate the cost, use the formula recommended in the article above. Dates can be replaced by references to date cells.

          =SUMIFS(B2:B20,A2:A20,">="&DATE(2023,1,31),A2:A20,"<="&DATE(2023,2,14))

  8. Hi, I'm not sure if any advice has already addressed this but i figured I'd still ask because I'm having issues. I'm trying to sum the total cost of items if today is between the start and end date of contracts, so my chart is set up like this
    A B C D E F G H
    ID # Manufacturer Model Asset Value Contract # Contract Vendor Start Date End Date
    1 Manufacturer 1 Model 1 $100,000.00 12345 Equipment Repair Company 1 9/30/2020 9/30/2024
    2 Manufacturer 2 Model 2 $100,000.00 12346 Equipment Repair Company 2 10/1/2019 10/1/2024
    3 Manufacturer 3 Model 3 $75,000.00 12347 Equipment Repair Company 3 5/1/2017 5/1/2022
    4 Manufacturer 4 Model 4 $75,000.00 12345 Equipment Repair Company 1 9/30/2020 9/30/2024

    And this is as close as I've been able to get with the formula
    =SUMIFS(D2:D5,today,">="&G2:G5,today,"<="&H2:H5)

    Any help or advice would be appreciated

    1. Hello!
      Please carefully study the recommendations and examples in the article above.

      =SUMIFS(D2:D5,G2:G5,">="&TODAY(),H2:H5,"<="&TODAY())

      I hope my advice will help you solve your task.

  9. Hello,

    Could you please help me with the following. I am attempting to get the average value for a specific date, and a unique reference from another tab. Trying to build IF statement but it keeps failing.

    1. Eg.

      The unique reference in a data sheet is B07RVJH98X_IF94837_EU (it can have multiple values depending on the EU country so trying to use the average) for a date 10/10/2022, 20/10/2022 and so on

      1. Hi!
        Please clarify your problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking.

        1. Hello,

          My apologies for not being more specific, but I managed to sort out my issue with this formula "=SUMIFS('REPORT'!$S:$S,'REPORT'!$B:$B,$F4,'REPORT'!$K:$K,Q$3)". However, now I am facing another difficulty. Is it possible to build an IF statement that will bring the latest value for a specific reference "XYZ" if the value is 0 for the current date?

          E.g.

          In column A - I have a unique reference, in rows B to R I have specific dates (10/12, 20/12, 30/12, 10/01 etc and so on) so I am bringing value for the date and my unique reference from the REPORT sheet. However, what I would like to achieve is - if there is no current value for the 10/01 then bring the latest from e.g. the 30/12 or 20/12.

  10. Very helpful. Just one question. I am working with a data set comprised of void transactions over a year. Each row is a specific void transaction, with columns for date, employee, reason for void, amount, etc. Every time I try to do the 'sumifs' for a date range I get 0. The big difference I noticed between the data in your examples and mine is that in yours every row (representing a transaction) is a different date. I have a lot of transactions on the same day. Just to see what would happen, I changed all the dates in my set to be different from one another. Then the formula works. I'm sure there has to be a work around or something. Thank you

  11. Dear all please help how to sum between two dates

    Month Rate Per Day Start date End Date
    January-23 $225.81 15-01-23 07-02-23
    February-23 $250.00
    March-23 $225.81
    April-23 $233.33
    May-23 $225.81
    June-23 $233.33
    July-23 $225.81
    August-23 $225.81
    September-23 $233.33
    October-23 $225.81
    November-23 $233.33
    December-23 $225.81

  12. Sir,,

    Please help in suming up sales, within specific time in a day. Eg
    10 Jan 23
    Like
    10:00 AM 10 pieces
    10:50AM 50 pieces
    11: 45AM 20 pieces

    I need to count same way sum of sales between 10 AM to 11AM which should come 60 automatically

  13. Column A Buy Date | Column B Sell Date | Column C Profit
    31/3/2022 | 31/3/2022 | $100
    31/5/2022 | 31/5/2022 | $50
    31/3/2022 | 10/4/2022 | $20
    31/8/2022 | 31/8/2022 | $10
    11/4/2022 | 30/4/2022 | $100

    Any formula that can do a sum if of profit if Days held is
    0 ($160 in this case: row 1, 2, 5)
    0-7 days ($0 in this case)
    8-14 days ($20 in this case: row 3)

    Trying to do this without adding an extra column in that does Col B - Col A
    15-22 days ($100 in this case: row 5)

      1. Thank you sir!

  14. duty shift& time name revenue
    Morning 08-02 A 200
    Morning 09-03 b
    Morning 10-04 A 152
    evening 08-02 b
    evening 09-03 A 100
    evening 10-04 b 3

    count formula to get no of staff worked day and night separately (blank cells not worked)

  15. Thanks! That was the fix!

  16. Hi, thanks in advance for any help, i'm struggling to find a solution but I'm sure it's simple!
    I'm trying to sum a range if another range contains the current year, year-1 and current year +1 i.e.

    Dec 2021 = £300
    Jan 2022 = £100
    Feb 2022 = £400
    Jan 2023 = £200

    Output should be 2021 = £300 2022 = £500 2023 = £200

    I've tried =SUMIFS(B15:M15,B14:M14,”>=”&DATE(C7,1,1),B14:M14,”<=”&DATE(C7,12,31))

    B15:M15 are costs per month. B14:M14 are dates pulled through from another sheet.
    C7 is current year ( calculated by the formula =YEAR(A1) where A1 is today's date)

    1. Use the below:
      For Previous Year: SUMIFS(16:16,15:15,">="&"01/01/"&C7-1,15:15,"="&"01/01/"&C7,15:15,"="&"01/01/"&C7+1,15:15,"<="&"12/31/"&C7+1)

      1. Prev Year: SUMIFS(16:16,15:15,">="&"01/01/"&C7-1,15:15,"<="&"12/31/"&C7-1)

        1. Current Year: SUMIFS(16:16,15:15,">="&"01/01/"&C7,15:15,"<="&"12/31/"&C7)

          1. Next Year: SUMIFS(16:16,15:15,">="&"01/01/"&C7+1,15:15,"<="&"12/31/"&C7+1)

    2. Hello!
      Unfortunately, I don't understand what the problem is. The formula calculates the amount for the year from cell C7.

  17. hi i want unique id based sum "= 5 Year" with one month date range........can you please help me ?

    Nikshya ID Date = 5 Year
    20286998 04-10-2021 2 4
    20286998 04-10-2021 0 0
    20285948 04-10-2021 5 6
    20285948 04-10-2021 0 0
    20363127 04-10-2021 2 2
    20363127 04-10-2021 0 0
    20363127 04-10-2021 0 0
    20363127 04-10-2021 0 0
    20162933 05-10-2021 1 2
    20162933 05-10-2021 0 0
    20162933 05-10-2021 0 0
    20162933 05-10-2021 0 0
    20162933 05-10-2021 0 0
    20162933 05-10-2021 0 0
    20522006 05-10-2021 2 2

    1. Hi!
      Unfortunately, I do not understand what you are trying to calculate. Describe your problem in more detail. Write an example of the expected result.

  18. I have 50 employees, I need to sum the total labels picked in a time frame of an hour:
    example EEID# 1111 between the hours of 10:00 to 11:00 he picked 8 labels

    EEID# Total Labels Time
    1111 - 3 - 10:01
    1111 - 5 - 10:20

    1. Hi!
      Have you tried the ways described in this blog post? Use time instead of a date condition. For example

      =SUMIFS(B2:B10, C2:C10, ">=10:00", C2:C10, "<=10:59")

      Or use the TIME function to specify the time. ">"&TIME(10,0,0)

  19. Hi, can anyone support, please? I need to sumif quantity of units picked in the warehouse by specific user from time 13:00-14:00.
    For example, this person "2345" have picked some quantity of units, and I need to filter out how many units this person did per hrs. It's 8hrs working day, and he picked all day, and I need to split by individual hrs. I hope this make sense?Thank you

    1. Hello!
      You can use the formulas recommended in the article above. Add the necessary conditions to them.
      For example,

      =SUMIFS(B2:B10,C2:C10,">="&F1,C2:C10,"<="&G1,A2:A10,H1)

      F1 and G1 - start and end time. H1 - person.

  20. I am trying to have my salary auto update on the last working day of every month any help would be appreciated

  21. I am trying to figure out a formula to count time in hours but the date and time are in the same category which makes it hard to
    do the calculation. I need this calculation to determine how long we have had trucks on our yard. Below is an example

    Arrived_in_Yard Hit_the_Dock
    9/5/2022 16:27 9/8/2022 8:50

    How could I calculate this time in hours?

  22. Hi, I have a list of production hours per day for Aug month. 2 columns - Dates & Prod hours. In a separate cell, I need a formula to give me the sum of hours only for Sundays worked. Can you help please? Below is the data set for example:
    Dates Prod Hours
    Mon 8/1/22 113.68
    Tue 8/2/22 104.42
    Wed 8/3/22 123.18
    Thu 8/4/22 93.24
    Fri 8/5/22 84.70
    Sat 8/6/22 77.82
    Sun 8/7/22 68.29
    Mon 8/8/22 116.15
    Tue 8/9/22 135.33
    Wed 8/10/22 131.12
    Thu 8/11/22 99.14
    Fri 8/12/22 99.45
    Sat 8/13/22 83.66
    Sun 8/14/22 75.60
    Mon 8/15/22 112.50
    Tue 8/16/22 124.28
    Wed 8/17/22 131.24
    Thu 8/18/22 92.03
    Fri 8/19/22 100.12
    Sat 8/20/22 75.53
    Sun 8/21/22 74.86
    Mon 8/22/22 200.38
    Tue 8/23/22 179.04
    Wed 8/24/22 162.54
    Thu 8/25/22 131.83
    Fri 8/26/22 130.99
    Sat 8/27/22 68.26
    Sun 8/28/22 79.09
    Mon 8/29/22 111.81
    Tue 8/30/22 122.09
    Wed 8/31/22 124.22

    Thank you

  23. I'd like to sum up the quantity of one item in a month that is produced in yesterday and today, and I have this table below. Is that possible to use SUMIFS function? Let says today is 08/04/2022 and I want to sum up quantity of item B in today and yesterday.
    Item 8/1/2022 8/2/2022 8/3/2022 8/4/2022 8/5/2022 8/6/2022 8/7/2022 8/8/2022
    A
    B 14000 14000 14560 14000
    C
    D
    E
    F 9000 12800

    1. Hello!
      Use the MATCH function to find the position of the cell with the current date. Use the OFFSET function to create links to 2 cells. Use these cells to create a condition in the SUMPRODUCT formula.

      =SUMPRODUCT(A2:Z2,(A1:Z1=OFFSET($A$1,0,MATCH(TODAY(),$A$1:$Z$1,0)-2))+ (A1:Z1=OFFSET($A$1,0,MATCH(TODAY(),$A$1:$Z$1,0)-1)))

      Hope this is what you need.

      1. Thanks for your support. That helps. However we have another case, let says there are few items in the table are duplicated, e.g 2 item A, 4 item B, 5 item C, each of them in a row. So which function need to use to sum up quantity of those items which have been produced yesterday and today?

        1. Hi!
          Add a row to your spreadsheet that will calculate the sum for each column. Use the formula I gave you with this total row.

  24. Dates Sale
    15-Jan-13 9
    15-Jan-13 2
    6-Oct-14 7
    16-Nov-09 8
    18-Jun-13 8

    Output should be

    Month Sale
    Jan 11
    Feb 0
    Mar 0
    Apr 0
    May 0
    Jun 8
    Jul 0
    Aug 0
    Sep 0
    Oct 7
    Nov 8
    Dec 0

  25. Hi guys,

    I am trying to do a =SUMIFS with a DATE RANGE and a wild card (notsure if thats the correct term)

    This is the formula that I am working with but it is either going "error" or just "zero".

    The date format from the data sheet is "10/16/2021 15:56:34"

    =SUMIFS('TDJ Sales Data'!$G$3:$G,'TDJ Sales Data'!$F$3:$F,">="&C1,'TDJ Sales Data'!$F$3:$F,"<="&C2"),'TDJ Sales Data'!$A$3:$A,"Armstrong"")

    1. Hi!
      I can't check the formula that contains unique references to your workbook worksheets.
      I didn't find a situation where A3:A works (in Excel functions).
      If you want to refer to a range starting from A3 to the max row you can use this formula

      OFFSET(A3, 0, 0, ROWS(A:A)-2)

      Read more here: OFFSET formula to define an Excel dynamic named range.

      For example,

      =SUMIFS(OFFSET(A3, 0, 0, ROWS(A:A)-2),OFFSET(A3, 0, 0, ROWS(A:A)-2)," > "&C1)

  26. I have a spreadsheet for payroll where I have people who worked multiple shifts and I would like a column with a total for both shifts if there are 2. This is my lay out:

    Employee name (which would have to match), Date worked(this will have to match as well), hours for that shift (this is what I want to combine if possible)

  27. Good afternoon,

    I have two columns. 1 column with the date (25-04-2022 to 30-06-2022) and 1 column with a quantity. I want to add up the quantity per date. For example, for the date 25-4-2022, there are 20 columns with a quantity. How do I get an overview of the total quantities per date? I want to do this because I want to use this data to make a graph of the number of sales per day.

    As an example:
    Date; Quantity
    25-04 -2022; 3
    25-04-2022; 4
    25-04-2022; 8
    26-04-2022; 3
    26-04-2022; 6
    26-04-2022; 5
    27-04-2022; 3
    27-04-2022; 8
    27-04-2022; 3
    etcc

    1. Hi!
      Have you tried the ways described in this blog post?
      Try the following formula:

      =SUMIFS(B2:B10, A2:A10, "="&DATE(2022,4,25))

      Use the DATE function to set the date you want. Or use a cell reference with a date.

  28. Hello and thank you in advance!
    I am trying to simply to add recorded hours for the week from Sunday-Saturday. The data exists in two simple columns, date in one, hours in the other. But, the days worked vary from week to week (sometimes a few days, sometimes all 7, hence, there will be varying numbers of rows between Saturdays). I would like a formula that finds "Saturday" in column A, then sums the hours for that calendar week (ie, that Sat and the preceding six days). Here's a sample three weeks of that data:

    Monday, March 18, 2019 10.03
    Tuesday, March 19, 2019 9
    Thursday, March 21, 2019 17.38
    Friday, March 22, 2019 9.72
    Saturday, March 23, 2019 5.52
    Monday, March 25, 2019 10.47
    Friday, March 29, 2019 9.28
    Saturday, March 30, 2019 9.47
    Sunday, March 31, 2019 11.45
    Monday, April 1, 2019 11.37
    Tuesday, April 2, 2019. 11.03
    Saturday, April 6, 2019 5.95

    1. The problem is compounded by the fact that not every Saturday is a workday, so in some weeks the last day worked in a calendar week might be a Thursday to Friday.

      1. Hello!
        Use the WEEKNUM function to determine the number of a week that begins on Sunday. Write the formula in column C :

        =WEEKNUM(A1,1)

        Use the SUMIF function to calculate your weekly total. Enter the following formula in cell D1 and then copy it down along the column:

        =IF(WEEKDAY(A1,2)=6,SUMPRODUCT(--($C$1:$C$20=WEEKNUM(A1,1)),$B$1:$B$20),"")

        You can add subtotals for column C to your table.
        I hope this will help, otherwise don't hesitate to ask.

  29. Hi,

    I'm using this for a personal month budget and I noticed that when I put in a month's date range, and then enter the next month's entries, it adds it to the previous month.

    For instance, let's say I have $1000 of income from 6/1 to 6/30 but when I put in income for July, it adds it to June's total AND July. Is there a way to have it only calculate for the dates in the formula and in between? Here is what I'm using:

    =SUMIFS(Input!B:B,Input!A:A,">=6/1/2022",Input!B:B,"<=6/30/2022")

      1. That was it! Thank you so much for your response.

  30. If one Date falls between 1st Jan2015 to 31st Dec'15, then if should show as X
    1st Jan2016 to 31st Dec'16, then if should show as Y
    1st Jan2017 to 31st Dec'17, then if should show as Z

  31. HI, I hope you can help me.

    What formula to use if I need to get the total amount received, for example, date range is from March 18-30, or April 1-15 and multiply it by 500.

    Thanks.

    1. Hello!
      Use the SUMIFS function to calculate the sum by condition. Please have a look at this article above.
      This should solve your task.

  32. Hi All,

    I have a small challenge which I am facing.

    I have following columns in my sheet
    1. Project ID
    2. Business Unit (BU)
    3. Start Date
    4. End Date

    I have an ask here to show the count for all Active projects

    My data needs to be

    BU1 BU2 BU3
    2022
    JAN 10 7 14
    FEB 13 6 11
    MAR and likewise for all other below months
    APR
    MAY
    JUN
    JUL
    AUG
    SEP
    OCT
    NOV
    DEC
    AND LIKEWISE FOR 2023 TILL ALL YEAR END date where Active Project means that has a start date = the month and year.

    In above example at a glance I should be able to see how many projects are active in 2022, how many are active in Jan, Feb etc, how many are active in future years as well till my last project ends

    can someone help?

  33. 11/25/2021 $1,506,500.00
    12/2/2021 $39,854.50 $1,466,645.50
    12/9/2021 $39,854.50 $1,426,791.00
    12/16/2021 $39,854.50 $1,386,936.50
    12/23/2021 $39,854.50 $1,347,082.00
    12/30/2021 $39,854.50 $1,307,227.50

  34. I have a sheet with a big bill. Payments are being remitted weekly. My sheet has a date column for the payment date. Payment amount. Remaining balance as of that day.

    I'm trying to get a updated balance for this sheet in another sheet.

    How can I have the balance update only weekly in another sheet?

    1. Hello!
      You can use XLOOKUP function to find the last match for a given date. See an example here: XLOOKUP last match.
      I hope I answered your question. If something is still unclear, please feel free to ask.

  35. Hi, I wish to calculate a Years of Service in employment minus a period of absence.
    eg: date of joining: 01MAR12
    years of service end date for calculation: 31DEC21
    period of absence: 03OCT16 to 02OCT17
    I can calculate the Years of Service range but unable to deduct the period of absence in the same formula - is that possible?

    1. Hello!
      I don't know what formula you are using. Calculate the number of years from 01MAR12 to 03OCT16 and the number of years from 02OCT17 to 31DEC21. Then count their sum.

      1. Ok then, but it did not work to just count the Sum, as I want the results to be Years, Months, Days format (using DATEIF formula). Using my example dates above, how would I add in excel two different periods: 01MAR12-03OCT16 with 03OCT17-31DEC21, so the final answer is in Years, Months, Days, as you can't add results of two DATEIF answers to get the final answer.

        1. Hello!
          In the DATEDIF function, you can only use one date to convert days to year - month - day.

          =DATEDIF(0, A2, "y") & " year " & DATEDIF(0, A2, "ym") & " month " & DATEDIF(0, A2, "md") & " days"

          Count the number of days, as I recommended to you earlier, and then apply this formula.

  36. Thanks for an interesting topic. Personally, I'm facing this problem, but with an added dimension:

    2019 2020 2021 2022

    January 1,600 1,780 1,860 1,890
    Feb 1,600 1,780 1,860 1,890
    March 1,600 1,780 1,860 1,890
    April 1,780 1,860 1,890 1,890
    May 1,780 1,860 1,890
    June 1,780 1,860 1,890
    July 1,780 1,860 1,890
    August 1,780 1,860 1,890
    Sept 1,780 1,860 1,890
    October 1,780 1,860 1,890
    Nov 1,780 1,860 1,890
    Dec 1,780 1,860 1,890

    I want to generate a sum, say, between two dates the user inputs on a separate sheet:

    Start Date 01/10/2019
    End Date 01/03/2021
    Total Amount 22,080

    Which is the most efficient formula to use and automatically pick up the relevant reference(s) and getting the sum...without going VBA.

    1. Hi!
      If the months in column A are written in numbers (1,2,3 ... 12), then the amount can be calculated using the formula:

      =SUM(B2:B13*(DATE(B$1,$A2:$A13,1)>=$F$1)*(DATE(B$1,$A2:$A13,1)<=$F$2))+SUM(C2:C13*(DATE(C$1,$A2:$A13,1)>=$F$1)*(DATE(C$1,$A2:$A13,1)<=$F$2))+SUM(D2:D13*(DATE(D$1,$A2:$A13,1)>=$F$1)*(DATE(D$1,$A2:$A13,1)<=$F$2))

      F1 and F2 - start and end date.

      I'm not sure if I understood you correctly, as I do not understand how the number 22080 is calculated.

  37. Hi
    I have a problem how to sum folowing
    Artikel Date free i
    630196 10/03/2021 2
    630196 10/03/2021 8
    630196 10/03/2021 6
    630196 31/03/2021 0
    630196 21/06/2021 0
    630196 13/07/2021 8
    630196 13/07/2021 2
    630196 13/07/2021 12
    630196 13/07/2021 6
    630196 10/08/2021 0
    630196 12/08/2021 0
    630196 08/09/2021 40

    I neet to do the sum for each row(in my case material code 630196) that is lover than date 19/11/2021
    Means in first row 2 in second 10 and so one
    Pleas help because I try do do this for two days bu no results

    Milan

  38. Thanks so much, I really learnt how to use SUMIFS formula and it helped me to master my cost tracker.
    thanks again and best regards
    Madjid

  39. I am needing assistance on how to sum two dates to a count.
    For example in cell M16, I have a start date of 6/11/2021 and in cell N16, I have the end date of 6/30/2021 and now I need to count the amount of days from start to end in cell O16. Can someone assist with me the formula to be able to do that?

  40. I have a very similar formula referencing raw data on another sheet, I have created a date column for normalized dates for this function to reference however it keeps doing total sum for all raw data and is not taking into account the date ranges at all. I am trying to reference fields for rolling 12 month calculations on several different sheets of data.

    I have confirmed all dates are formatted as dates, used the same normalization formula on another sheet and it is working.

    SUMIFS('SFDC Closed Opp Export'!L:L,'SFDC Closed Opp Export'!B:B, ">="&A1, 'SFDC Closed Opp Export'!L:L, "<="&B1, 'SFDC Closed Opp Export'!V:V, A6)

    Column L on Raw data is number
    Column B is the normalized date for close date
    column V is the segment

    1. Hello!
      Unfortunately, your problem is not clear to me. The formula contains links to your worksheets and is therefore difficult to understand. Give an example of the initial data and the desired result.

      1. I actually figured it out - thank you

  41. Data contain the date with time (actual transaction time is capturing) eg; 29-08-2021 2:36:27 PM, 29-08-2021 2:53:17 PM like wise,

    want to sumif to date wise only means date of 29-08-2021 , how could formulate it

  42. Great, I have used to determine the bank's balance in my accounts for a given date, so now I can sum by the debit and credits columns for all data less or equal than the current date and compare with the real data in bank's account and both data in a different column for each daily date.

    Accounting Balance = SUMIFS(debits,date,"<="&given-date,Bank,"Citibank")-SUMIFS(credits,date,"<="&given-date,Bank,"Citibank")

    I appreciate your post, thank you very much. Simple and effective.

  43. Hi, I'm using a sumifs function for each month's totals with the year referenced in a separate sheet. It works great for every month except Feb/March. I used the range "="&DATE(DASHBOARD!$C$2,2,1),$B:$B,"<="&DATE(DASHBOARD!$C$2,2,29))

    1. Not sure why half my message is cut off. For Feb Entries I allowed "<=" 2-29 to allow for leap years but now entries for Mar 1 show up in BOTH Feb. and Mar. totals. How do I include all dates between the beginning and end of each month allowing for leap year without problems? Thanks.

      1. Hello!
        To determine the end date of the month, use the function EOMONTH :

        =EOMONTH(DATE(DASHBOARD!$C$2,2,1),0)

        Read more here.

  44. Hello,

    I am sincerely hoping you can help me. I'm trying to find a formula to add in monthly rent on the first day of the month to an equation which will then subtract it from the amount that they paid that month to give me a running total of the amount that they owe

    Rent Due Running Total January Febraury
    of Money Owed
    1000 ?? 850 900

  45. I have a cash flow on one sheet of an excel spreadsheet with a balance for each month of the year. each month aligns to UK tax year so April runs from the 6th of April to the 5th of May and so on. Using The TODAY() function I am trying to the show the current monthly balance from the cashflow on another sheet.

    Can you assist?

    1. Hello!
      If I understand your task correctly, the following formula should work for you:

      =SUMPRODUCT(D1:D15,--(C1:C15>=EOMONTH(TODAY(),-1)+6),--(C1:C15<=EOMONTH(TODAY(),0)+5))

      You can learn more about EOMONTH function in Excel in this article on our blog.

  46. I am trying to do a sumifs formula on a range of dates, however It keeps returning a zero

    05 May 2020 14,256.00
    01 May 2020 12,356.00
    04 May 2020 12,548.00
    02 May 2020 12,536.00
    06 June 2020 12,586.00
    08 June 2020 12,365.00
    10 May 2020 12,356.00

    Find Data between ranges: 01 May 2020 - 10 May 2020

    =SUMIFS(C2:C8,B2:B8,">''&F2",B2:B8,"<''&G2")

    1. Hello!
      The formula below will do the trick for you:

      =SUMIFS(C1:C8,B1:B8,">"&F2,B1:B8,"<"&G2) I hope it’ll be helpful.

      1. It says that there is a problem with this formula

  47. It would be great to get some help if possible. I have a spreadsheet which tracks transactions and values between certain dates, and I am trying to do a sum dependent on a certain date criteria i.e how much value was gained in the month of April.

    I followed your steps, and have a formula of:

    =SUMIFS('EXP April-June 2021'!AF2:AF5131,'EXP April-June 2021'!I2:I5131,">="&DATE(2021,4,1),'EXP April-June 2021'!AF2:AF5131,"<="&DATE(2021,4,31))

    And it returns a figure for me, but it's about £10m short of what it should be when I use the highlight & count approach on those dates.

    I cannot work out why it would be doing this. All of the dates are formatted as dates, and the values are formatted as currencies.

    I have tested this on the results for May & June so far, and the formula works in that it returns a different figure for each month, and updates as I add more to the source data, but doesn't calculate the correct figure currently.

    1. Hello!
      I can't see your data but I guess the problem is in DATE(2021,4,31). No such date exists. The formula DATE(2021,5,31) returns May 31, 2021 00:00:00. Therefore, the last day of the month is ignored. Use DATE(2021,6,1) instead of DATE(2021,5,31). Perhaps this will help.

    2. Nevermind... I fixed it. I accidentally put the value data set in the formula twice rather than the date data set. Thanks for your guides!

  48. S.NO DATE AMOUNT
    1 19-04-2021 -8,930.00
    2 20-04-2021 20,250.00
    3 22-04-2021 7,047.20
    4 23-04-2021 -27,000.00
    5 26-04-2021 -4,200.00
    6 27-04-2021 -
    7 28-04-2021 10,500.00
    8 29-04-2021 -12,000.00
    9 30-04-2021 -3,200.00
    How to calculate total -ve value of every month; +ve value of particular month & count for zero by sumifs & countif functions.
    Please help me. I am waiting for your solution.
    Thanks
    prakash

  49. Hello,

    I'm having right trouble with this formula. I can't work out what i've done wrong! I'm trying to calculate price by customer within a set date period.

    =SUMIFS(G8:G20, B8:B20, "=>"&B5, B8:B20, "=<"&C5, M8:M20, "="&A5)

    Can someone identify what's wrong with it?
    Column G is price
    Column B is date
    Column M is customer

    A5 is customer name
    B5 is the start date
    C5 is the end date

    I've checked the dates are in date format and the numbers are in number format
    I've interchanged the signs
    I've added and removed the "="& for the customer name
    I've added spaces and removed them.
    None of these have resulted in anything. The Formula box remains blank.

    Help please!!

  50. I'm trying to summarize some product counts by different date ranges. For example, in column C, I have several products listed from rows 13 thru 50 with some products in a similar category repeating and the counts are in columns to the right with dates as headings (ie: 6/28/21, 6/29/21, thru 12/31/21). I need to summarize by product and then by different date ranges (ie: weekly vs monthly) but am not sure the best way to do this in Excel. All the examples I see have all the data in columns including the dates, but in my case I don't have that.

    GL Acct 06/28/21 06/29/21 06/30/21 7/01/21 07/02/21 07/03/21....
    Filling 1 10 15 12 5 6 4
    Filling 2 4 1 3 20 11 2

    So, I need to be able to sum the Fillings into 1 number for Jun that includes the numbers from 6/28 and 6/29 and then summarize the Fillings into 1 total for Jul that includes 7/1 thru 7/3.

    Any help is appreciated.

    Kat

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)