COUNTIFS for each week of the quarter

ScionoftheNight

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!

Paul Newcome

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.

ScionoftheNight

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.