2

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.

2

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:

8
  • Please remember that as per site guidelines when an answer addresses your question, you should accept it and even upvote it so others can benefit as well. Feb 19 '21 at 23:20
  • After some more digging, trial and error, the following query that should produce something doesn't output anything and I have no idea why even tho it seems correct: =QUERY(A$1:B; "SELECT A, COUNT(B) WHERE B >= date '2021-02-15' AND B <= date '2021-02-21' GROUP BY A ORDER BY A ASC"; 1)
    – Andrei
    Feb 20 '21 at 14:39
  • Please see the updated answer. As for "I wasted a lot of time since I...", if you just copy and paste the formula from the answer, Google is smart enough to change it to your locale. Feb 20 '21 at 17:18
  • Wonderful, it works! Any way to make it also work with dates that have "dd-mm-yyyy" format?
    – Andrei
    Feb 20 '21 at 18:00
  • "Any way to make it also work with dates that have "dd-mm-yyyy" format?" Have you tried it? If not do so and if still having issues please share a test sheet. Feb 20 '21 at 18:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.