nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Using @row in cross-sheet reference formula

dsprague

Hello -

I am trying to use the SUMIFS formula on a sheet titled "Budget" to return all values if two conditions on another sheet titled "Purchase Log" sheet are true. 1st condition on the sheet "Purchase Log" that is in the column titled "Program" and 2nd condition is in the column titled "Type". I want the value in "Program" to change by row and the value in "Type" will always be "FCO-12". I want to make this a column formula, but the one i am using (below) is returning #UNPARSABLE and i can't figure out what is wrong.

It works if i make the reference for {PRGRM} absolute, for instance in this row it is "ems":

=SUMIFS({Approved Range 2}, {PRGRM}, "ems", {Purchase Log Range 1}, "FCO-12")

But when i try and convert it to change by row, then it returns #UNPARSABLE:

=SUMIFS({Approved Range 2}, {PRGRM}, Program@row, {Purchase Log Range 1}, "FCO-12")

Finally, i

Thank you!

Find more posts tagged with

Accepted answers

Andrée Starå

https://community.smartsheet.com/discussion/147130/using-row-in-cross-sheet-reference-formula

Hi,

I hope you're well and safe!

Try something like this.

=SUMIFS({Approved Range 2}, {PRGRM}, [Program/Station]@row, {Purchase Log Range 1}, "FCO-12")

Did that work/help?

I hope that helps!

Be safe, and have a fantastic day!

Best,

Andrée Starå | Smartsheet Expert Consultant & Partner / CEO @ WORK BOLD

✅ Did my comment/answer help? Please support with 💡 ⬆️ ❤️ ✅ so others find it. Thank you! 🙏

All comments

Jesse G.

Hi @dsprague

You might want to give these 2 below a try, the first one is with the brackets commonly used in Smartsheet, second one where you literally place the '=' sign.
=SUMIFS({Approved Range 2}, {PRGRM}, [Program]@row, {Purchase Log Range 1}, "FCO-12")

=SUMIFS({Approved Range 2}, {PRGRM}, =[Program]@row, {Purchase Log Range 1}, "FCO-12")

However, it might be something else in your destination ranges aswell, since i tried to replicate most of what is given here in a sheet, and al 4 (2 of you, 2 of me) formula's worked.

Hope this helps.

dsprague

I modified the cross-references so they are more clear and incorporated this suggestion which didn't seem to work either, still coming back with #UNPARSABLE. I am adding some snippets of the actual sheets in case that helps.

Here is the modified formula:

=SUMIFS({Total Cost}, {Program}, =[Program]@row, {Type}, "FCO-12")

The first snippet is the sheet where i am placing the formula, in the FCO-12 Expenses column:

The Boat Program row is using the formula you suggested.
The Cancer Prevention and EMS are using the direct reference "Cancer Prevention" or "EMS"

And here is the source sheet…

Andrée Starå

https://community.smartsheet.com/discussion/147130/using-row-in-cross-sheet-reference-formula

Hi,

I hope you're well and safe!

Try something like this.

=SUMIFS({Approved Range 2}, {PRGRM}, [Program/Station]@row, {Purchase Log Range 1}, "FCO-12")

Did that work/help?

I hope that helps!

Be safe, and have a fantastic day!

Best,

Andrée Starå | Smartsheet Expert Consultant & Partner / CEO @ WORK BOLD

✅ Did my comment/answer help? Please support with 💡 ⬆️ ❤️ ✅ so others find it. Thank you! 🙏

dsprague

I think that worked! I am headed to a meeting but will come back and verify.

dsprague

That fixed it! Thanks all

Andrée Starå

https://community.smartsheet.com/discussion/comment/513309#Comment_513309

Excellent!

Happy to help!

✅ Remember! Did my comment/answer help? Please support with 💡 ⬆️ ❤️ ✅ so others find it. Thank you! 🙏

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.