CODEWITHSUNDEEP
excelexcel-formula
denlau
denlau

Reputation: 1016

Count all unique customers in date range

I have a big spreadsheet with order data. And I have a column containing the "customers first order date" (lets call it column A), and then I have "order data" (lets call it column B).

What I want to achieve is something like this - except the comparison of col A and col B doesn't work in COUNTIFS, and I can't get my head around how I could solve it:

=SUM(COUNTIFS(Sheet1!$A$2:$A$20000;"="&Sheet1!$B$2:$B$20000;Sheet!$A$2:$A$20000;">="&Sheet2!E$2;Sheet1!$A$2:$A$20000;"<="&Sheet2!E$3))

Is there a way to do this with countifs?

My data could be something like:

Col A Col B ID
01-01-2021 00:01:00 01-01-2021 00:01:00 1
01-01-2021 00:01:00 05-01-2021 20:11:33 1
01-04-2021 00:01:00 01-04-2021 00:01:00 3
01-03-2021 00:01:00 01-03-2021 00:01:00 2
01-04-2021 00:01:00 24-04-2021 17:51:29 3
01-04-2021 00:01:00 01-05-2021 19:55:59 3
01-04-2021 00:01:00 04-06-2021 04:22:01 3
01-04-2021 00:01:00 24-06-2021 18:09:44 3
29-04-2021 00:01:00 04-06-2021 04:22:01 4
29-04-2021 00:01:00 24-06-2021 18:09:44 4

With this data, what I would expect from the forumula - if the start and end dates are in march - I want "1", since that is the amount of the unique IDs with col A date in the range. If it is in january, I altso want "1", since that is the amount of unique ID's with dates in column A that matches the range. And if it is april, I want "2", since that is again the amount of IDs in the range.

Upvotes: 0

Views: 369

Answers (1)

denlau
denlau

Reputation: 1016

I think Mathieu helped me out. So this is what seems to do the job:

=COUNTA(UNIQUE(FILTER(Sheet1!A2:A20000;(Sheet1!C2:C20000>=Sheet2!$E$2)*(Sheet1!C2:C20000<=Sheet2!$E$3);"")))

Upvotes: 1

Related Questions