I'm still quite the beginner when it comes to more complex formula in Google Sheets. I'm trying to do an analysis for my company, the data is in a db-like table (CSV import), one of the columns contains a date, another one a status. I want to count how many times a certain status falls into a given week.

Here's an example (sheet "data"):

First three rows are week 44, the others week 43. I want a table that looks like this:

So example for cell B2 of the result table have =DCOUNTA(data!A1:B8,"Status",{"Status","Date";"created",[WEEK == A2]})
(what I need in square brackets)

The problem I have is that the WEEK function expects an input, so how can I circumvent that? Does the DCOUNTA function work for this or do I need another function?

Thanks for your help!

CodePudding user response：

use:

=INDEX(QUERY({ISOWEEKNUM(B2:B), A2:A}, 
 "select Col1,count(Col1) 
  where Col2 is not null 
  group by Col1 
  pivot Col2 
  label Col1'week'"))

update:

=INDEX(IFNA(VLOOKUP(A1:A, QUERY({ISOWEEKNUM(raw!C2:C), raw!B2:B}, 
 "select Col1,count(Col1) 
  where Col2 is not null 
  group by Col1 
  pivot Col2 
  label Col1'week'"), SEQUENCE(1, COUNTUNIQUE(raw!B2:B)) 1, )))

CodePudding user response：

Alright, here's how I've solved my issue.

I added an additional column to my raw data with

=CONCATENATE(ISOWEEKNUM(L2),"-",IF(AND(MONTH(L2)=1,ISOWEEKNUM(L2)>1),YEAR(L2)-1,YEAR(L2)))

so I have the week number and the year in a single cell (it's a historical analysis). The if-clauses are for cases where the first days of the year are part of the last week of the previous year.

I could then easily access what I need by creating a table that has all the [week-year] combinations as header and the users in the first column of the lines. Each cell looks something like this:

=DCOUNTA(RAW!$A:$O,"start",{"user","start";$B9,C$1})

where B9 holds the user name and C1 said string combination.