Help with Cross-Sheet SUMIFS Formula for Multi-Select Column Reference

MeganF

Formula Tried:

=SUMIFS({Sheet - TCO Cloud Cost Savings Monthly Savings}, HAS({Sheet - TCO Cloud Cost Savings Business Application}, [Business Application]@row), {Sheet - TCO Cloud Cost Savings Savings Status}, "Complete")

Error: Incorrect argument set

I'm trying to create a metrics sheet that sums values from another sheet based on multi-select column matches. My formula keeps returning an "incorrect argument" error.

What I'm trying to accomplish: I need to calculate the total sum of [Monthly Savings] where:

1. The [Business Application] column contains the value from [Business Application] in the current row
2. The [Savings Status] equals "Complete"

=SUM(IF(HAS({Sheet - TCO Cloud Cost Savings Business Application}, [Business Application]@row), ….

I can get the formula to work on the primary sheet, but I have to provide the Business Application name in quotes.

On page formula:

=SUMIFS([Monthly Savings]:[Monthly Savings], [Business Application]:[Business Application], HAS(@cell, "App Name"), [Savings Status]:[Savings Status], "Complete")

Since the application list is so long, I prefer to use the metrics sheet, making the formula set to the column, so that as applications are added, the reporting dashboard will automatically reflect it.

=Chris Palmer

Hello @MeganF

SUMIFS() expects a range, criteria, range, criteria… pattern

HAS() is a function, not a range or criteria

You might want to try a helper column inside the source sheet named like
"Matches Business Application". In that column have the following formula:

=IF(HAS([Business Application]@row, [Business Application]$1), 1, 0)

In the metrics sheet reference the helper column:
=SUMIFS({Sheet - TCO Cloud Cost Savings Monthly Savings},
{Sheet - TCO Cloud Cost Savings Matches Business Application}, 1,
{Sheet - TCO Cloud Cost Savings Savings Status}, "Complete")

MeganF

Thank you. I tried your suggestions and could not get it to return without an error message.

In the past (different companies), I have used a metrics sheet for cross-sheet references in which "COUNTIF" was used for specific tags in multi select columns.

What complicates this case is the cross-sheet reference, looking for the business applications, a "complete" status, and a total $ amount for all rows that contain the business application@row.

The temporary workaround is using the sheet summary with the application in quotes. It returns the results, but will not be manageable as the intake collects more data and uses one of the 50 applications not in the sheet summary.
Example: (Works in the source sheet)
=SUMIFS([Monthly Savings]:[Monthly Savings], [Business Application]:[Business Application], HAS(@cell, "AI"), [Savings Status]:[Savings Status], "Complete")