COUNTIFS for each week of the quarter
Hi, in my job we gene edit cells and afterwards, we see which percentage of the population received the edit (range 0-100%).
I am trying to track how many orders fall into which range (0,%, 1-9%, 10-19%... 90-100%) for each week of the quarter. This information is pulled from a master sheet where we keep track of all of our orders.
I have attached a picture of what the tracking sheet looks like. The portion of the formula that is relevant to my question is bolded :
=COUNTIFS({ICE Analysis Range 1}, "1st", {ICE Analysis Range 2}, =0, {ICE Analysis Range 3}, AND(@cell >= DATE(2019, 10, 1), @cell <= DATE(2019, 10, 6)), {ICE Analysis Range 4}, =0)
Right now, I would have to go into every single cell and manually enter the date range. Is there a way to get this automated?ย
Essentially, I'm asking how to do a COUNTIFS with a date range. Thank you!
Comments
-
The easiest way to do this would be to add two helper columns that you can later hide.
ย
Make them both date columns, enter the date range, then reference those cells.
-
Ah but I would have to do that for every single week though, right? I would end up having lots of helper columns with that.
