CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
Robin
Robin

Reputation: 33

Google Sheet - Filter by ROW and COLUMN

I'm trying to do something seems simple, but dunno how to.

I would liken to create simple timesheet report by week Here is an example of what I want to do enter image description here

But I don't know how to filtering by row (project or employee) AND grouping/summing by column (week number). I tried with filter, query, {range1\ range2} etc.

The expected resultats are in green on the sheet. It can be one formula for all datas or one formula by cell, it's not a problem for me

Google sheet doc example : https://docs.google.com/spreadsheets/d/14H8GgMCWCDXuHm5q34Nz1kxUR12NO3tQqOeQeOOjaDY/edit?usp=sharing

Thanks a lot for all the time you will spend to help me :)

Best regards

Upvotes: 1

Views: 524

Answers (1)

player0
player0

Reputation: 1

try:

=INDEX(QUERY(SPLIT(FLATTEN(IF(C5:I="";;B5:B&"×"&C2:I2&"×"&C5:I)); "×"); 
 "select Col1,sum(Col3)
  where Col2 is not null
  group by Col1
  pivot Col2
  label Col1'Employee'"))

and:

=INDEX(QUERY(SPLIT(FLATTEN(IF(C5:I="";;A5:A&"×"&C2:I2&"×"&C5:I)); "×"); 
 "select Col1,sum(Col3)
  where Col2 is not null
  group by Col1
  pivot Col2
  label Col1'Project'"))

enter image description here

Upvotes: 1

Related Questions