Sumif rows and drag down to increment column (index - match)

Question

I have a sheet with rows for tasks and columns for staff. I am trying to sum the total for each staff for each section of tasks on a different sheet.

Here is what my data looks like:

Task#   Role 1  Role 2  Role 3
1           
1.005   1       3       2
1.01    2       2       4
2           
2.001   1       3       4
2.03    6       1       3

And my expected output looks like this:

Role    Task 1  Task 2
Role 1  3       7
Role 2  5       4
Role 3  6       7

I have a formula that is working but I had to hard-code in the row numbers for each group of tasks:

=SUM(INDEX(Matrix!$12:$36,0,ROW(1:1)-1+16))

In my formula, Matrix!$12:$36 are the rows that correspond to the first group of tasks and ROW(1:1) is role 1. So, when I drag the formula down it returns the sum for each role for that section of tasks.

But, I realized that in some old versions of the workbook, there were different numbers of tasks for each section so I think I need to make it a SUMIF based on the task number.

I did see a couple of similar questions but I'm not familiar enough with index formulas to get it to work. Appreciate any tips on how to adjust my formula to incorporate a sumif, or other ideas to accomplish this.

Thank you! Alex

Scott Craner Scott Craner · Accepted Answer · 2020-02-21T21:55:36

This works with full columns and does not matter how many per:

=SUMIFS(INDEX($B:$D,0,MATCH($F2,$B$1:$D$1,0)),$A:$A,">="&COLUMN(A:A),$A:$A,"<"&COLUMN(B:B))

Sumif rows and drag down to increment column (index - match)

1 Answers