Google-sheets – Counting rows based on the date it has and the current end of the week

google sheetsgoogle-sheets-datesgoogle-sheets-query

Problem to solve:
Given 2 rows, containing the author's name and a date, you have to count how many rows that author has.
The tricky part here is that I have to use the date row and check how many entries does that author have within the week of that date he entered.

What my data looks like:

enter image description here

Expected output:

enter image description here

Let's hypothetically say that today is the 19th of February and it's a Friday.
I need to count each row that has a date between 15th of February (Monday) and 21st of February (Sunday).

Next week, from 22nd of February, I need to start it over and check it again for that week and so on.

So, I'm trying to better understand how to approach a simple / stupid problem that I can't find a solution to it.
I've been messing around with the QUERY() function but with no luck since I have literally no idea what lead to follow.

Best Answer

EDIT

(based on OP's comment)

... I need to count them only if they are part of the same week.

Use the following formula

=QUERY(ARRAYFORMULA({A1:A, B1:B, WEEKNUM(B1:B,2)}), 
             "select Col1, count(Col3)
              where Col1 is not null 
                and Col3="&WEEKNUM(TODAY(),2)&"
              group by Col1, Col2
              label count(Col3) 'in this week' ", 1)

enter image description here

The trick here is the creation of a virtual array holding the week-number.

If you want to just group them based on weeks, just omit the and clause and Col3="&WEEKNUM(TODAY(),2)&"

(Do not forget to adjust for your locale. If still in trouble please read how to and share a test sheet so as you can be easier helped.)

ORIGINAL ANSWER

Please use the following QUERY formula

=QUERY(A1:B,"select A, count(B) 
               where A<>'' 
                  and B <= DATE '"&TEXT(TODAY(),   "yyyy-mm-dd")&"' 
                  and B >= DATE '"&TEXT(TODAY()-7, "yyyy-mm-dd")&"' 
               group by A",1)

enter image description here

(I believe your image of shown expected result is wrong since it just counts all values and is not based on the dates within the current week)

Functions used:

Related Topic