Search code examples
google-sheetsgoogle-sheets-formulaformulaweek-number

'weeknum' function

Here is what I'm trying to do:

I have three Spreadsheets.
(1) Days (holds increase in user-nr per day)
(2) Weeks (is supposed to sum up user increases so they are shown each week)
(3) Months (is supposed to sum up user increases so they are shown each month)

To give an example: if we have 10 users on Monday, 20 more on Tuesday, 15 more on Wednesday (that's when the next calendar week starts), then I want in the sheet "weeks" to see e.g. 45 users in calendar week 27 or so.

So what I try is this: =SUMIF(WEEKNUM(Days!A2:A977); A2; Days!B2:B977)
A holds the date of the day
B holds the number of users.

What happens is it does not sum up the number of the users shown in B, but only gives the number in the first cell of the weeknumber shown in A2.

What is my mistake?

Solution

  • The formula seems to be correct, but two things are needed.

    1. You should embrace it in ArrayFormula()
    2. You should use one more weeknum()
    =ArrayFormula(sumif(weeknum(Days!A2:A977);weeknum(Days!A2);Days!B2:B977))