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: 73

  1. Hi , I am getting wrong result in Sumif. Below is the data set

    B1:x1=Week 49 Week 50 Week 51 Week 52 Week 53 Week 1 Week 2 Week 3 Week 4 Week 5 Week 6 Week 7 Week 8 Week 9 Week 10 Week 11 Week 12

    Weekly jobs done B2:X2 =0 0 0 0 1 0 1 0 2 1 0 1 1 1 0 2 1

    Criteria= Week 4
    Formula used =SUM(B2:F2)+SUMIF(I1:X1,"<="&A3,I2:X2)
    Jobs done till Week 4= 7 result by formula
    result should be 4
    Thanks in advance
    priyank

    1. Hello!
      You can use this formula:

      =SUM(B2:F2)+SUMIF(G1:X1,"<="&A3,G2:X2)

      This formula gives the result 4

  2. Just to say "Thank You! It helps!" our charming Excel wizard! :) .... and to ask "Can you do "AND" logic for two or more conditions with "SUMIF()"?

  3. Hi, I can't get this to work when having the date range in columns. Here's the formula I'm using:
    =SUMIFS(v0[@[2021-04-01]:[2021-04-30]], $C$2:$AF$2, ">="&Report!$B$1, $C$2:$AF$2, ",<) signs, it return 0 even if the = is kept. So it is something with the range config that is not correct.
    Can you help?

    Note, date format is correct (Scandinavian pc). Resending the post as only part of the text were published.

    1. No point in trying to use this for questions any longer, as the comment function unfortunately doesn't work properly

      1. Hi, I can't get this to work when having the date range in columns. Here's the formula I'm using:
        =SUMIFS(v0[@[2021-04-01]:[2021-04-30]], $C$2:$AF$2, ">="&Report!$B$1, $C$2:$AF$2, ",<) signs, it returns 0 even if the = is kept. So it is something with the range config that is not correct.
        Can you help?

        1. Hi,
          I cannot validate your formula as it contains unique references. Explain what the expression means v0[@[2021-04-01]:[2021-04-30]] ?
          Include an example of the source data and the result you want to get. It’ll help me understand the problem you faced better and help you.

          1. Note, date format is correct (Scandinavian pc).
            Hope it makes sense as I had to publish in separate comments

          2. =SUMIFS(v0[@[2021-04-01]:[2021-04-30]], $C$2:$AF$2, ">="&Report!$B$1, $C$2:$AF$2, "<="&Report!$C$1)

            1. The sheet (v0) has a table where the headers are dates (the entire April in this case) located in columns C to AF on row 2. Each row in the table contains numbers for each date. The sheet "Report" contains a start date at B1 and an end date at C1.

              1. When using this formula it returns 0 and no error message. As a test, I changed the start/end date to the same date, and changed the formula to only exact match (=), the I get a result for the date in question. But if I add the higher/lower (>,<) signs, it return 0 even if the = is kept. So it is something with the range config that is not correct.

          3. no, still not publishing...

          4. As I been posting, all text is not published. But trying once more, now with the missing part only.
            =SUMIFS(v0[@[2021-04-01]:[2021-04-30]], $C$2:$AF$2, ">="&Report!$B$1, $C$2:$AF$2, ",<) signs, it return.....

  4. Hi, I can't get this to work when hvaing the date range in colums. Here's the formula I'm using:
    =SUMIFS(v0[@[2021-04-01]:[2021-04-30]], $C$2:$AF$2, ">="&Report!$B$1, $C$2:$AF$2, ",<) signs, it return 0 even if the = is kept. So it is something with the range config that is not correct.
    Can you help?

    Note, date format is correct (Scandinavian pc).

    1. The text was not published in full

    2. the comment field is not working, it cuts part of the text...

  5. From an invoicing perspective, each day there is a charge for service (prorated amount) based on a monthly amount.

    I would like to calculate the dollar amount for a service for 'Date A' through 'Date B' (both Date A & B are billable days).

    The spreadsheet to include a user defined 'Date A' (ie: 01/15/21), 'Date B' (ie: 04/16/21) and 'Monthly $' (ie: $10.00)

    January there are 17 billable days (01/15/21 thru 01/31/21) * 0.0323 (daily prorate) = $5.48 (prorated amount of the 'Monthly $')
    February there are 28 billable days = $10.00 ('Monthly $')
    March there are 31 billable days = $10.00 ('Monthly $')
    April there are 15 billable days (04/15/21 thru 04/30/21) * 0.5000 (daily prorate) = $5.00 (prorated amount of the 'Monthly $')

    A1 = 'Data A'
    A2 = 'Date B'
    A3 = 'Monthly $'
    A4 = ? - hoping for a formula here

    1. Hello!
      If I got you right, the formula below will help you with your task:

      =DATEDIF(A1,A2,"m")*A3 + (EOMONTH(A1,0)-A1+1)*A3/(EOMONTH(A1,0)-EOMONTH(A1,-1)) + (A2-EOMONTH(A2,-1))*A3/(EOMONTH(A2,0)-EOMONTH(A2,-1))

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

      Hope this is what you need.

    2. Amended:

      April there are 15 billable days (04/01/21 thru 04/15/21) * 0.0333 (daily prorate) = $5.00 (prorated amount of the 'Monthly $')

  6. I am tracking productivity for a department and want to provide weekly updates to our team in the form of previous week averages. I'm trying to create a quick calculator where I can simply change the Start/End Dates and it recalculates the averages automatically.

    The formula I have below keeps returning @DIV/O! errors. C2:KW2 is the portion of the row where daily productivity will be input. C1:KW1 is the row with each days date in it. A36 and A39 are the cells that are dynamic and where I would input the date range I am trying to calculate.

    =AVERAGEIFS(C2:KW2, C1:KW1, ">="&A36, C1:KW1, "<="&A39)

    1. Hello!
      If there are no dates in the C1: KW1 range that match your conditions, then the formula will return an error. Check how you enter your dates. Perhaps they are written in the form of text.

  7. I would like to do a similar thing but kind of opposite and was wondering if you can help.
    We have a spreadsheet with events (multiple days) and numbers of staff e.g.

    Start date End date Number of staff
    1 Jan 21 3 Jan 21 5
    2 Jan 21 2 Jan 21 3
    5 Jan 21 10 Jan 21 10

    What I would ideally like is to then have a second sheet with all the dates of the year in column A and then how many staff are working on that date so with the above it would look like
    1 jan 21 5
    2 jan 21 8
    3 jan 21 5
    4 jan 21
    5 jan 21 10
    6 Jan 21 10

    Etc....

    Is that possible?

    Thanks

    Andy

    1. Hello!
      I hope you have studied the recommendations in the tutorial above. It contains answers to your question.

      =SUMIFS(Sheet1!$C$1:$C$3,Sheet1!$A$1:$A$3,"<="&Sheet2!A1,Sheet1!$B$1:$B$3,">="&Sheet2!A1)

      Hope this is what you need.

  8. I am trying to develop an Excel formula to obtain a count.

    If an ID number appears multiple times in one column and the result “received” appears one or more times in a different column, what is the formula to count only 1 instance of the occurrence of the ID and “received”?
    For example:

    Column Column
    F J
    5596326 attempted
    5596326 delivered
    5596326 received
    5596326 received
    5596326 sent
    5596326 Wrong Number

    The ID number appears 6 times in column F, and “received” appears twicein column J. With a correct formula, the result should be 1.

    What is the formula?

    Thank you for any assistance.

    1. Hi,

      Not sure if I understood your ask here, but try below formula in H1.

      =COUNTIFS($F$1:$F$6,F1,$G$1:$G$6,G1)

      5596326 attempted 1
      5596326 delivered 1
      5596326 received 2
      5596326 received 1
      5596326 sent 1
      5596326 Wrong Number 1

Post a comment



Thanks for your comment! Please note that all comments are pre-moderated, and off-topic ones may be deleted.
For faster help, please keep your question clear and concise. While we can't guarantee a reply to every question, we'll do our best to respond :)