Sum of data between dates

MattSibleyPowr

Hi,

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?

Hollie Green

=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.

Paul Newcome

Your second COUNTIFS excluded the

{87A}, 1

range/criteria set.

Paul Newcome

You would need to use a SUMIFS.

Hollie Green

=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.

MattSibleyPowr

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

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.

MattSibleyPowr

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?

Regards,

Paul Newcome

Your second COUNTIFS excluded the

{87A}, 1

range/criteria set.

MattSibleyPowr

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

Paul Newcome

Happy to help. 👍️