SUMIF matching rows and columns from another sheet?
I’m trying to create a resourcing table where for various projects, we complete how much time they will work on a project in the coming weeks. This is not a timeline or Gantt chart, so it doesn’t have dates, but it does have a list of all the projects and the hours are distributed over the weeks of the work. Instead it has across the top the weeks of the year.
In a second table, we want to summarize how many hours are allocated for each person in each week. I can have a different column for each formula, but I’d love to have it match the first row (number of the week) as a criterion in the first table (again, looking to match the number of the week from that table). But I can’t quite get it to work.
If the formula is done separately for each column, then its as follows:
=SUMIF({PersonResourced}, Person@row, {DatesHours})
But if I want to add a criterion to match also the column number, then I thought a SUMIF formula would work, but I can’t get it down:
=SUMIFS({DatesAndHours}, {PersonResourced}, Person@row, {DateRow}, [9/7]1)
DatesAndHours is all the columns from W1. PersonResourced is the Person column. {DateRow} is the first row where its written what week of the year it is.
Any thoughts?
Answers

It looks like you're using the template set Staff Plan Management, is that correct?
If so, you're correct, the template Master Rollup has the SUMIF statement with each column referenced separately for the column to SUM at the end of the statement.
Ex, for your Rollup column 9/6:
=SUMIF({PersonResourced}, Person@row, {Wk 1 Column})
then for your column 9/7:
=SUMIF({PersonResourced}, Person@row, {Wk 2 Column})
The SUMIFS doesn't work the way you have it set up because it can't take the number from the first row to identify what column you want to SUM. Instead, we'll want to use an INDEX Function to find the correct column based on the number in that top row.
Try something like this:
=SUMIF({PersonResourced}, $Person@row, INDEX({DateRow}, 0, [9/6]$1))
Then you can dragfill this across all your columns to autoupdate per person and per week/column.
The INDEX Function works like this:
INDEX(range, row_index, column_index)
This is why we first list the range (all your dates/numbers), then 0 as we don't have a specific row, and then finally the 1st row to find a match for the column number.
Let me know if this works for you!
Cheers,
Genevieve
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 10.5K Get Help
 61 Global Discussions
 46 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 54 Brandfolder
 124 Just for fun
 50 Community Job Board
 466 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!