SUMIFS Multi-Column Reference Cross Sheet Row and Column Criteria

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

Find more posts tagged with

Accepted answers

All comments

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?

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.