Is there a way to SUM a COLLECTION from a twodimensional range
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 twodimensional 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.
Answers

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
bassam.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

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.

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!
bassam.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
Help Article Resources
Categories
Check out the Formula Handbook template!