SUMIF matching rows and columns from another sheet?

Dominique Rose Winther

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?

Genevieve P.

Hi @Dominique Rose Winther

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 drag-fill this across all your columns to auto-update 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