Is there a way to SUM a COLLECTION from a two-dimensional range

Randy Saad

I have a data sheet containing a hours per week. Each week is its own column and each person may have one or more rows. I am trying to create a formula in a summary sheet that will look up this two-dimensional array and sum the hours for each person in each week. I can find a correct value using an INDEX, MATCH, MATCH formula, but I'm having trouble summing a collection of values. I know I can create ranges for each week in my summary sheet, but I would like to avoid that. The raw data look like this...

The summary sheet would have one row for each person and one column for each week. I'd like to create one formula in the summary sheet to sum the collection of hours for each person each week.

Bassam Khalil

Hi @Randy Saad

Hope you are fine, i created a sample for you please check the following screenshot & the formula for each column:

1- the working hour database is "Working Hour Data"

2- the summary database "Working Hour Summary"

3- the formula to calculate each week for each employee is:

A- W1 formula =SUMIFS({Working Hour Data wh1}, {Working Hour Data employee}, Employee@row)

B- W2 formula =SUMIFS({Working Hour Data wh2}, {Working Hour Data employee}, Employee@row)

C- W3 formula =SUMIFS({Working Hour Data wh3}, {Working Hour Data employee}, Employee@row)

D- {Working Hour Data wh1} reference is as in the following screenshot, you do the same for wh2,wh3 by selecting the whole column w2,w3

E- {Working Hour Data employee} reference is as in the following screenshot

Randy Saad

Thank you Bassam. But I'm looking for one formula for entire summary sheet. I want to avoid having to make a reference for each column in the hours database.

Bassam Khalil

Hi @Randy Saad

 Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!