SUMIF Criteria link to another cell vs typing in

Lauren Ford

Hello!

I'm trying to get a sense of whether or not this is possible — or if I need to use a different formula.

I have a sumifs formula that is working great. The trouble is, I have a bunch of different criteria that goes in the quotations as the searchable feature. I'm wondering if it's possible to link the searchable data to an existing cell rather than typing it in over and over for each variation of the formula.

Current formula:

=SUMIFS({Total Amount}, {Test - Career Education Grant Submission Range 2}, "Accounting", {Category}, "Administrative Costs")

Ideal formula:

=SUMIFS({Total Amount}, {Test - Career Education Grant Submission Range 2}, "@Value25", {Category}, "@Program3")

It would be even more super ideal if I could "lock in" one of the criteria so that it was consistent across multiple secondary criteria. Kind of like the way the $ locks in the row or column in an excel formula. So something like…

=SUMIFS({Total Amount}, {Test - Career Education Grant Submission Range 2}, "@$Value$25", {Category}, "@$Program3")

So the info at @Value25 would be consistent. And then if I copied the formula down the column, the info along the "Program" column would just update to "Program3"…Program4…Program5….etc

Is this possible?

I have 15 criteria1 elements, and 18 criteria2 elements. Anything to help me not have to type in each criteria1 across the intersecting criteria2 would be amazing.

Thank you!

Protonsponge

Hello @Lauren Ford,

If I have read your question correctly, I think what you are looking for is absolutely possible. We could make use of the @row feature in Smartsheet to avoid the need to lock in a specific cell reference, such as $Value$25.

In the demo below the [Total Amount] is being returned from the reference sheet, where i) the {Criteria 1}, is as per [Criteria 1]@row and ii) where {Criteria 2} is as per [Criteria 2]@row.

An advantage of building it this way is that we can set this to a column formula and it will return the [Total Amount] for each row based upon the information in [Criteria 1] and [Criteria 2]. So for your various Criteria 1 and Criteria 2 elements, you would just need to type them in to the appropriate columns in the formula sheet and the sum will be returned where both criteria are present in your reference sheet.

=SUMIFS({Total Amount}, {Criteria 1}, [Criteria 1]@row, {Category}, [Criteria 2]@row)

Formula Sheet Demo -

Reference Sheet Demo -

I hope that is along the lines of what you are looking for and that some of the above is helpful to you in some way,

Protonsponge 😊