How do I reference data to count Checked boxes that fall within certain date ranges?

Jacqueline15

Hi all,

I am wanting to count the number of Checked boxes for the column HackerRank Sent in the sheet

"2. Screenings" (Pictured below) that are found between dates 5/02/22 and 5/06/2022, and put this number in row 4 of the # Completed Column ( where #UNPARSEABLE is currently at) . Everything I have tried keeps giving me the #UNPARSEABLE error. This is the current formula that I am using :

=COUNTIFS({2. Screenings Range 3},">="&[Start Date]4,{2. Screenings Range 3},"<="&[Due Date]4,INDEX({2. Screenings Range 3},0,MATCH("Hacker Rank Sent",{2. Screenings Range 2},0)),TRUE)

I will have to replicate this formula for the other Tasks found on this sheet as well. Any help here would be great!

Shimanta Roy

You can use the following formula to count the checked boxes in "HackerRank Sent" column:

=COUNTIFS([HackerRank Sent]:[HackerRank Sent], True, [Screened Date]:Screened Date], >DATE(2022,5,2),[Screened Date]:Screened Date],<=DATE(2022,5,6))

Jacqueline15

That works if I put it on my 2.Screenings sheet, but not if I put it in my 1. PV Goals Sheet. I need to reference these into a different sheet

Shimanta Roy

Oh, that's easy. You have to refer to the 2.Screenings sheet. To use the formula in PV Goals/Actions sheet you can use the same format as above formula, just change the ranges. Remove the ranges one by one for more convenience. Firstly, remove the [HackerRank Sent]:[HackerRank Sent] part in the formula. Don't remove any commas. You can find the option "Reference Another Sheet". Click on that, you new floating window will come where you can browse and select the 2.Screenings sheet. Select the "HackerRank Sent" column by clicking on the column name. Then click on "Insert Reference". This will define your first range. Similarly, replace the [Screened Date]:[Screened Date] range with Screened Date column by referring to 2.Screening sheet in the same process.

Jacqueline15

Thank you!