2

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.

Improve this question
2
  • Welcome. Your questions (1 & 2) are very different form each other. Please split them in two separate questions. Also, do read how to and share a test sheet so as you can be easier helped. – marikamitsos Feb 18 '20 at 18:07
  • Thanks @marikamitsos . Will do. I'm a bit new here so the help is appreciated. – Nomadik Feb 18 '20 at 18:12
2

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:

Improve this answer
8
  • Do you mind breaking down how that line works? I'm only a bit better with excel than I am with stack. – Nomadik Feb 18 '20 at 20:46
  • 1
    No problem. First of all, did it work for you? – marikamitsos Feb 18 '20 at 20:57
  • It did not, but I'm not sure what variables I need to edit in it. – Nomadik Feb 18 '20 at 21:06
  • 1
    Could you please share a test sheet? – marikamitsos Feb 18 '20 at 21:09
  • 1
    "Thank you! I deduced the syntax from some testing." What do you mean? Did the formulas work for you or not? If not we should try finding out why. If yes, as per site guidelines when an answer addresses your question, you should accept it and maybe upvote it so others can benefit as well. – marikamitsos Feb 19 '20 at 14:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.