#Unparseable error, please help

Hi, sorry to bother, because I know this can be a very dumb question but I have tried all the ways with no luck.

I keep getting this error and I don't know where is the error. I want to sum a column called "2022OpEx" only if the sum of other 2 columns ("CarryOverAmnt2021" and "2022CapEx") are equal or higher than 250,000.

The formula getting this error is: =IF(SUM({CarryOverAmnt2021}, {2022CapEx}), >=250000), {2022OpEx})

Thanks so much in advance for any advise!

Find more posts tagged with

Formulas

Accepted answers

All comments

Brett Wyrick

Try this:

=IF(SUM([CarryOverAmnt2021]:[CarryOverAmnt2021], [2022CapEx]:[2022CapEx]) >= 250000, [2022OpEx]@row)

Xochitl Cerda

Thanks, Brett! I forgot to mention that all the references are from another smartsheet. I removed the last parenthesis I had after 250000, like this: =IF(SUM({CarryOverAmnt2021}, {2022CapEx}), >=250000, {2022OpEx}) but now I get error #Invalid data

Brett Wyrick

=IF(SUM({CarryOverAmnt2021}, {2022CapEx}) >=250000, {2022OpEx})

Try that?

I think you have an unnecessary comma prior to the ">=".

Also, is {2022OpEx} a range of cells or just one cell?

Xochitl Cerda

https://community.smartsheet.com/discussion/comment/312735#Comment_312735

if I remove the comma, I get now "#invalid column value" and {2022OpEx} is a column

Brett Wyrick

Got it.

Okay, so for this sort of formula, you can't utilize a range (in this case, a column) as an "output value" for an IF formula. In your IF formula, {2022OpEx} is a range, so that's why you're getting the "#invalid column value" response.

If this did not utilize cross-sheet formulas, you could simply use "@row" at the end of the column and it'd work. But since you're utilizing cross-sheet formulas, now we need to figure out a way to dynamically obtain the value you're looking for.

In other words, now this formula needs to get more complex. Is there any sort of ID or value that is the same on each sheet? If not, can there be?

Xochitl Cerda

https://community.smartsheet.com/discussion/comment/312740#Comment_312740

Yes, I have another column in common called "Project_ID"

Brett Wyrick

YES! Awesome. You'll need to create a reference to Project_ID column on the sheet you're pulling the data from. Then you could utilize an Index/Match function (or VLOOKUP if your columns will never change place) to find the particular cell you're looking for from the 2022OpEx column.

Xochitl Cerda

https://community.smartsheet.com/discussion/comment/312742#Comment_312742

Thanks Brett, but all the columns (Project_ID, CarryOverAmnt2021, 2022CapEx and 2022OpEx) are in a single smartsheet.

I only want to sum the total of "2022 OpEx" only if the sum of "CarryOverAmnt2021 + 2022CapEx" is = or higher than 250,000

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.