Sum of data between dates


Hope someone can help me, if this is at all possible.

I have a request form that my users use to request ink and I would like to use a formula to show me how much we have used for each Ink type per month or during the last 30 days.

Below is my sheet which when a user requests ink it auto fills a request date field and puts a 1 into a column for the ink requested (will only ever one be a 1)

I have managed to make a sheet showing the overall total of requests for each ink type and a separate formula showing the number of all requests made between certain dates but not a formula to show me both (for example how many 87A cartridges were ordered in the last 30 days).

Is it possible?


Best Answers


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    You would need to use a SUMIFS.

  • Hollie Green
    Hollie Green ✭✭✭✭✭
    Answer ✓

    =COUNTIFS({85A Quantity}, 1, {Requested Date}, >=DATE(2022, 7, 1)) should give you the number of 85A units ordered since July 1st you can replace that date with whatever date you would like it to calculate from. You will have to set up your references.

  • Thanks you for both taking your time to help me, Hollie that worked perfectly!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    I had assumed the number in the Quantity column could be different which is why I suggested a SUMIFS to give you how many were ordered as to opposed how many times they were ordered. Sorry for the confusion.

  • Hi,

    So I have managed to get the formula to give me the requested quantity from requests this week and year to date per row, but when trying to calculate the previous week it seems to be adding value of the whole column as below (should be 1 but shows as 2)

    I'm not overly experienced with these more advanced formula so I have been combing formula from the template sheets and this is what I have created:

    Working Correctly - Ink Requested this week: =COUNTIFS({87A}, 1, {Request Date}, IFERROR(WEEKNUMBER(@cell), 0) = WEEKNUMBER(TODAY()), {Request Date}, IFERROR(YEAR(@cell), 0) = YEAR(TODAY()))

    Working Correctly - Ink Requested Year to Date: =COUNTIFS({87A}, 1, {Request Date}, IFERROR(YEAR(@cell), 0) = YEAR(TODAY()))

    Not Working Correctly - =IF(WEEKNUMBER(TODAY()) = 1, COUNTIFS({87A}, 1, {Request Date}, IFERROR(WEEKNUMBER(@cell), 0) = 52, {Request Date}, IFERROR(YEAR(@cell), 0) = YEAR(TODAY()) - 1), COUNTIFS({Request Date}, IFERROR(WEEKNUMBER(@cell), 0) = WEEKNUMBER(TODAY()) - 1, {Request Date}, IFERROR(YEAR(@cell), 0) = YEAR(TODAY())))

    Can anyone you see where this formula is going wrong?


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    Your second COUNTIFS excluded the

    {87A}, 1

    range/criteria set.

  • How did I miss that! Thanks you so much :)

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Happy to help. 👍️

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!