SUMIFS in a 2-Dimensional Array, with 2 column conditions

[dach_cowi]

Hi, i have this table (could not upload mini sheet) on multible sheets week is merged cells but here i have just wrote 1/2 in all cells

Week:	1	1	1	1	1	2	2	2	2	2
user	aa	bb	cc	dd	ee	aa	bb	cc	dd	ee
task 1	1	4	2	3	2	2	8	4	6	9
task 2	3	5	9	3	2	7	4	2	8	3
sum	4	9	11	6	2	9	12	6	14	12

an other table in same workbook gives the names of all the sheets i want to summarize

eg.
project 1
project 2
project 3
project 4

Week	1	2
aa	sum user "aa" across sheets "project 1';project 2;project 3; project 4" for week 1	sum user "aa" across sheets "project 1';project 2;project 3; project 4" for week 2
bb	sum user "bb" across sheets "project 1';project 2;project 3; project 4" for week 1	sum user "bb" across sheets "project 1';project 2;project 3; project 4" for week 2

i have tried many things to get it to work, and i got it to work until i added week number including summarize across the sheets with =SUMPRODUCT(SUMIF(INDIRECT("'"&named_range&"'!range"),criteria,INDIRECT("'"&named_range&"'!sum_range")))

Kindly Daniel

 

[dach_cowi]

no one?

 

[offthelip]

The way I would do that is to concatenate the first two lines into a single line, you could do this in the same table if you have room, however you could do it on an entirely different sheet which could be hidden. Then a simple index match will give you the offset to do a straight sumif. then sum the sumifs for all the projects. If you have a lot projects I would then use VBA to write the formula, to ensure no errors. If you do it using vba to write formula you then rerun it if you add more projects