What is the best approach for filtering rows and counting unique dates in a Google Sheet?

Question

I have a shared activity log of bike rides in Google Sheets that looks like:

[A] Date	[B] Name	[C] Miles
1 Nov 2, 2022	Joe	2.1
[2] Nov 2, 2022	Ray	1.5
[3] Nov 3, 2022	Ray	3.1
[4] Nov 4, 2022	Joe	2.9
[5] Nov 4, 2022	Joe	2.2

This will run the whole month. On another sheet I'd like to show the participants, their active days in the week and the total distance they rode.

Leaderboard for Nov 1 - 5 | [A] Name | [B] Active Days | [C] Distance | | --- | ----------- | -------- | | 1 Joe | =COUNTIFS(...) | =SUMIFS(...) | | [2] Ray | =COUNTIFS(...) | =SUMIFS(...) |

I created COUNTIFS and SUMIFS like the following:

Cell B2 for Joe =COUNTIFS(B:B,A1,A:A,">="&DATE(2022,11,1),A:A,"<="&DATE(2022,11,5))

Cell C2 for Joe =SUMIFS(C:C,B:B,A1,A:A,">="&DATE(2022,11,1),A:A,"<="&DATE(2022,11,5))

This worked well until I had the situation where someone did two activities in a day, as happens with Joe on Nov 4 (rows 4 and 5). The total miles is correct but I need to have the COUNTIFS take into account unique dates. My formula would report 3 when the answer should be 2.

A live Google Sheet recreating the above is here.

I've played with:

• FILTER: =FILTER(A:C, B:B = "Joe", A:A >=DATE(2022,11,1), A:A <= DATE(2022,11,5))
• DCOUNTA: =DCOUNTA(A:C,"Date",{{"Name";"Joe"},{"Date";">=Nov 1, 2022"},{"Date";"<=Nov 5, 2022"}})
• QUERY: =COUNT(UNIQUE(QUERY(Log!A:C, "select A where B = 'Joe' and A >=date'2022-11-1' and A <=date'2022-11-05'")))

The QUERY above seems to work feels a little wonky. I'm wondering if there are suggestions at better ways to go about this?

Answer 1

You can set a formula like this to stablish the count of unique days for each:

=counta(unique(FILTER(Log!A:A, Log!B:B = "Joe", Log!A:A >=DATE(2022,11,1), Log!A:A <= DATE(2022,11,5))))

You can automate it a bit more if you put the dates in cells. You could do something like this:

Then you can make a query for Names and Total Miles in A2:

=query(Log!$A:$C,"SELECT B,SUM(C) where B is not null and A >=date'"&text(B1,"YYYY-mm-dd")&"' and A <=date'"&text(C1,"YYYY-mm-dd")&"' group by B label SUM(C) 'Total Miles'",true)

And use this byrow formula in C3: =byrow(A3:A,lambda(each,if(each="","",counta(unique(FILTER(Log!A:A, Log!B:B = each, Log!A:A >=B$1, Log!A:A <= C$1))))))

Then you can just copy these three rows for each "Challenge Week" and only change the dates and the rest will be set up automatically!

Here you have a copy with this implemented ;) https://docs.google.com/spreadsheets/d/1zO5i699COY5omDjux7-V8EIx_64DiItX4OP3PE-uCFo/edit#gid=0