CODEWITHSUNDEEP
google-sheets
limonvnx
limonvnx

Reputation: 83

Countif value is unique and occurs in given month

I've got a sheet that tracks form responses and I'd like to add up the number of new users each month. I thought I could achieve this with a COUNTIFS formula like this:

=COUNTIFS('Form responses 1'!A1:A,"<01/02/2018",'Form responses 1'!B1:B,UNIQUE('Form responses 1'!B1:B))

So far I haven't had any success. Because it's a form response, column A is a timestamp and column B is the user's email address:

Timestamp             Email Address
01/01/2018 09:00:12   [email protected]
03/01/2018 10:15:31   [email protected]
06/01/2018 13:03:53   [email protected]
11/01/2018 09:43:11   [email protected]
15/01/2018 11:26:09   [email protected]
20/01/2018 09:00:12   [email protected]

I want a formula that will give the number of new users for each month in separate cells like this:

Month       New Users
January             3
February            6
March              10
etc.

Can anyone point me in the right direction?

Many thanks

Upvotes: 1

Views: 121

Answers (1)

Max Makhrov
Max Makhrov

Reputation: 18707

=QUERY( QUERY({A:B},"select Col2, min(Col1) group by Col2"), "select year(Col2), month(Col2)+1, count(Col1) group by year(Col2), month(Col2)+1 label year(Col2) 'Year', month(Col2)+1 'Month', count(Col1) 'Count New'")

  • query #1 (inner): find the minimal date for each name
  • query #2: count names by dates (year + month)

Upvotes: 2

Related Questions