Sum of matrix values, conditional, with infinite rows and columns

Question

I have 2 sheets: "Planning" and "Utilization" (example)

Planning: Employees assigned to projects for each week. Some projects are not fixed but need to be simulated (checkbox = true).

Utilization: Shows the utilization of each employee for each week. Only rows with:

a) no checkbox in Planning!A2:A
b) rows with checkbox checked
c) rows with project name in Planning!B2:B are to be considered.

I'd like to have a formula in Utilization!B2 that would calculate the sums for Utilization!B2:E4. With infinite rows and columns in Planning sheet.

score 1 · Accepted Answer · edited Jun 20 '20 at 09:12

1

try:

=QUERY(QUERY({Planning!A:H}; 
 "select Col3,sum(Col5),sum(Col6),sum(Col7),sum(Col8) 
  where not Col1 = FALSE 
    and Col3 is not null 
  group by Col3"; 0); 
 "offset 1"; 0)

UPDATE:

=ARRAYFORMULA(QUERY(QUERY({Planning!A:Z}; 
 "select Col3,"&
 TEXTJOIN(","; 1; IF(Planning!E1:1="";;"sum(Col"&COLUMN(E:Z)&")"))&" 
  where not Col1 = FALSE 
    and Col3 is not null  
  group by Col3"; 0); 
 "offset 1"; 0))

FIX for 'get' error:

=ARRAYFORMULA(QUERY(QUERY({Planning!A:D\Planning!E:Z*1}; 
 "select Col3,"&
 TEXTJOIN(","; 1; IF(Planning!E1:1="";;"sum(Col"&COLUMN(E:Z)&")"))&" 
  where not Col1 = FALSE 
    and Col3 is not null  
  group by Col3"; 0); 
 "offset 1"; 0))

edited Jun 20 '20 at 09:12

Community

1
1

answered Feb 05 '20 at 09:45

player0

124,011
12
67
124

thanx, but I need infinite columns and rows. – Mara Feb 05 '20 at 09:45
Hey player0. Thanx, works, but if I have only one entry for specific week, or if I have no entries for specific week, the formula breaks with error saying: "Error Unable to parse query string for Function QUERY parameter 2: TypeError: Cannot read property 'get' of undefined". Any idea why? – Mara Feb 05 '20 at 10:29
@player0 The double query with "offset by 1" to get rid of labels?! My first time seeing that. Very cool! Will have to try to remember to use it! – MattKing Feb 05 '20 at 14:12
@MattKing yes indeed – player0 Feb 05 '20 at 14:46
@Mara try: `=ARRAYFORMULA(QUERY(QUERY({Planning!A:D\Planning!E:Z*1}; "select Col3,"& TEXTJOIN(","; 1; IF(Planning!E1:1="";;"sum(Col"&COLUMN(E:Z)&")"))&" where not Col1 = FALSE and Col3 is not null group by Col3"; 0); "offset 1"; 0))` – player0 Feb 05 '20 at 14:59
@player0 , I have a follow up question. I've extended the above spreadsheet with 2 more sheets. Now I need to convert the percentages to hours (sheet "Potential"), based on the availability provided in sheet "Time". So for each day on x-axis, for each user in y-axis, there should be a lookup in "Time"-Sheet. Is there a way to achieve that? – Mara Feb 06 '20 at 09:24
I guess yes but not in an easy way. it would need to be totally dismantled, multiplied and the re-pivoted. I would need to test it further... – player0 Feb 08 '20 at 01:05

MattKing · Answer 2 · 2020-02-05T14:16:29.777

0

For infinite rows and Columns, I like to use OFFSET() so you might try this formula in A1 on a new tab.

=ARRAYFORMULA({QUERY(QUERY({Planning!A:D\N(OFFSET(Planning!E1;;;ROWS(Planning!E:E);COUNTA(Planning!E1:1)))};"select Col3, "&TEXTJOIN(",";TRUE;"SUM(Col"&SEQUENCE(COUNTA(Planning!E1:1);1;5)&")")&" where Col2 is not null group by Col3";0);"offset 1";0)})

edited Feb 05 '20 at 14:16

answered Feb 05 '20 at 14:11

MattKing

7,373
8
13

Mine is updated to reflect a new trick i learned from @player0 – MattKing Feb 05 '20 at 14:16

Sum of matrix values, conditional, with infinite rows and columns

2 Answers2

UPDATE:

FIX for 'get' error: