Google Sheets – Using Dates as Criterion for COUNTIFS

google sheetsgoogle-sheets-arrayformulagoogle-sheets-dates

So I'm currently making a recruiting data sheet for an online community. I'm trying to make it as automated as possible without actually making a bot to do it.

How can I make a COUNTIF(or similar) that counts the amount of people join within a time period?

My current strategy was to do this:

=COUNTIF(D3:D900,"2/\**$/2020")

Its not getting the expected results, but I'm also not quite good with Regex in Google Sheets. This strategy is attempting to get a result for the amount of people who joined in the month of February using the dates.

Best Answer

How can I make a COUNTIF(or similar) that counts the amount of people join within a time period?

You do not really need any regex function. You can use the different versions of the following formula instead.

  1. To find all values within the second month (February) 

=ARRAYFORMULA(COUNTIFS(V21:V38,"<>",MONTH(U21:U38), "="&2))

  1. To just find the Y values within the second month (February) 

=ARRAYFORMULA(COUNTIFS(V21:V38,"Y",MONTH(U21:U38), "="&2))

Have a look at the example I created in your sheet.

enter image description here

How the formula =ARRAYFORMULA(COUNTIFS(V21:V38,"<>",MONTH(U21:U38), "="&2)) works:

COUNTIFS looks into the first range U21:V21 and in cell V21 it calculates the value according to the criterion "<>" which means not empty.
It then goes to the next cell U21 where the function MONTH has already extracted the month from cell that in our case is different from the second criterion 2 and in this case COUNTIFS drops the value.
Then ARRAYFORMULA takes over and repeats the process in the next row and keeps on doing that till it hits the end of our range on row 38.
The final result is 6, which means only 6 pairs met both the criteria.

Functions used:

Related Topic