SUMIF(S) - Referencing a Range within a Range?

sean59916

Hi - 

I'm hoping that someone could look at this and provide a fresh take on it - my goal - is not to define 50+ ranges (representing individual columns)!

Use case: A sheet that tracks leave for individuals where columns are defined by week, as a user enters leave, the amount of time appears in the relevant column.

I would like to summarize by individual on another sheet (having the summary within the main sheet would be ideal, but, the range within the summary formula is not increasing dynamically when a new record is being added via a form).

Examples attached. 

 

Thank you.

Sean

 

 

 

 

 

 

 

 

 

 

 

 

Paul Newcome

It would take a little bit of time to set up, but you could use a helper column for each week (I know that's a lot, but...) with a unique identifier in row 1 of each column (a, b, c, d, e, f, g, etc.....). In those helper columns you could use 

 

=SUMIFS([Column15]$3:[Column15]@row, $[Resource Name]$3:$[Resource Name]@row, $[Resource Name]@row)

 

I am basing column and row references off of your screenshots. This will look from the top row to the current row and give a running total for that resource. If you do that, you will have a running total for each week. You will need to update the formula with the appropriate column names for each helper column. Then add in a Created (date) type automated column.

 

In your Summary sheet you would then use those same unique ID's across row 1 as you used in the helper columns of your Master Sheet. I will assume the actual column names are the same between master and summary. Then use something along the lines of

 

=INDEX({Full Range from Master Sheet}, MATCH(MAX(COLLECT({Created Column Range}, {Resource Name Range}, $[Resource Name]@row)), {Created Column Range}, 0), MATCH([Column15]$1, {Range Covering Row 1 of the Master Sheet}, 0))

 

Basically what this will do is look for the most recent Created (date) row for that resource and then pull whatever number is in the matching column from the master sheet (thus the reason for the unique ID's). This will match up your weeks for the columns because the column ID's in the Summary sheet match the Helper Column ID's in the Master sheet, and the helper columns contain a running total for each resource in the same row as each entry. Therefore, pulling the most recent entry for that particular resource will give you whatever that running total is for that week.