Google Sheets Get Sum of Daily/Weekly Counts for a specific range of cells as per condition

Question

I have a set of data in Google Sheet as shown below:

Row No.	A	B	C	D
-	Count	Person Name	Date	Status
1	163	ABC	03-07-2021	1. Done
2	26	ABC	03-07-2021	2. In Progress
3	35	ABC	03-07-2021	2. In Progress
4	21	XYZ	03-07-2021	1. Done
5	0	XYZ	03-07-2021	1. Done
6	17	ABC	04-07-2021	1. Done
7	0	ABC	04-07-2021	1. Done
8	267	ABC	04-07-2021	1. Done
9	29	XYZ	04-07-2021	2. In Progress
10	42	XYZ	04-07-2021	1. Done

And for these data, I wanted a tracker which shows all the people in Columns with all dates in Rows (because dates we have to add each day) alongwith the sum of the Count column for each date from the above data and only for ones which are marked "1. Done". So I prepared this tracker and following is the result which is needed as per the data above:

Row No.	A	B
Date	ABC	XYZ
03-07-2021	163	21
04-07-2021	284	42

I have already received a solution by doing a query, thanks to the people here! Solution - =QUERY(A:D, "select C,sum(A) where D starts with '1' group by C pivot B", 1)

Now I actually need weekly counts for each person based on the achieved results for a set of 5 business days in a week. No idea if something can be added to this query but thinking if there's a workaround for the same using formula for both daily & weekly counts.

Thanks in advance!

Answer 1

You can append your table with WEEKNUM(C:C) and group by that column to get the weekly counts:

=ARRAYFORMULA(QUERY({A:D,IFERROR(WEEKNUM(C:C),"Week Num")}, "select Col5,sum(Col1) where Col4 starts with '1' group by Col5 pivot Col2", 1))

If you need only the data from Monday to Friday, you can append dayofweek() to the query conditions:

=ARRAYFORMULA(QUERY({A:D,IFERROR(WEEKNUM(C:C),"Week Num")}, "select Col5,sum(Col1) where Col4 starts with '1' and dayofweek(Col3) > 1 and dayofweek(Col3) < 7 group by Col5 pivot Col2", 1))

Sample Output:

References:

WEEKNUM()

Google Query Language

Answer 2

Not exactly sure if this is what you want, but this will give you weekly summaries by person:

=arrayformula(query({A:D,weeknum(C:C,2),weekday(C:C,2)},"select Col5,sum(Col1) where Col4 starts with '1' group by Col5 pivot Col2 label Col5 'Week number' ",1))

This will ignore weekends:

=arrayformula(query({A:D,weeknum(C:C,2),weekday(C:C,2)},"select Col5,sum(Col1) where Col6 <=5 and Col4 starts with '1' group by Col5 pivot Col2 label Col5 'Week number' ",1))

You can alter the start of the week and weekday if you need to:

WEEKNUM

WEEKDAY