SUMIFS Multi-Column Reference Cross Sheet Row and Column Criteria

scamiolo

My goal with this formula is to sum the total across a range of columns based on the values in columns and rows.

I don't have an issue when the reference range is limited to a single column, but I'm getting an #INCORRECT ARGUMENT SET error when attempting to reference an array of columns (which will work for Supply but NOT for Demand), and incorporating a criteria that assesses the header row in the source sheet, and only sum those that match in the destination sheet column@row.

My source sheet: Resource supply by portfolio for each period/month.

My destination sheet: Resource supply/demand/variance by portfolio for each period/month.

Destination sheet formula:

=SUMIFS({Resource Supply by Portfolio}, {Department}, $Department@row, {Period}, [Period 1]$1, {Header Row}, $Portfolio@row)

Resource Supply by Portfolio range below

Melissa Yamada

Hello @scamiolo

SUMIFS only support 1 column for sum range.

So for PD Supply your {Resource Supply by Portfolio} should only be for the PD Supply column.

You'll need to create 1 SUMIFS per portfolio.

scamiolo

Thanks @Melissa Yamada! I can make due with that for the supply, but I'm struggling with an approach for demand.

My source sheet includes a header row that specifies the portfolio, with individual columns for each project within a portfolio.

What I'm looking to achieve here is the same as supply, but I would like to sum based on portfolio. Any thoughts on an approach to achieve this?

Melissa Yamada

Hello @scamiolo

Since you have different columns per portfolio, your formula should look at 1 portfolio per formula.

For example:

For Supply:

=SUMIFS({Resource Supply Column}, {Department}, $Department@row, {Period}, [Period 1]$1)

For Demand:

=SUMIFS({Demand Column}, {Department}, $Department@row, {Period}, [Period 1]$1)

scamiolo

@Melissa Yamada Thanks! I follow, however I'm still not sure how to get this to work for Demand, since the demand for each portfolio is captured across a variety of rows, with new projects being added removed over time. I would like to avoid having to create a formula in the source sheet to summarize total demand for each portfolio to limit risk of human error.

Is there a work around you can think of..? Maybe creating a separate sheet to summarize by Portfolio?

Melissa Yamada

@scamiolo can you share a screenshot of your columns/rows that are for demand? want to make sure I am aligned with how your sheet is set-up.

scamiolo

@Melissa Yamada Sure! See below.

Row 1 includes the acronym for the portfolio. I'm looking to sum all columns for a given portfolio in a separate sheet which includes the supply, and calculate variance.

Looking to them plot all of this but haven't gotten there yet.

Melissa Yamada

@scamiolo which columns refers to the demand?

scamiolo

@Melissa Yamada

Demand is captured for each resource across 10+ columns, each column is tied to a specific project, which funnels up to a portfolio. I need to be able to analyze resource demand compared to supply at the portfolio level AND collect demand on the project level for expense reporting.

Any ideas?