#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!
Answers

Try this:
=IF(SUM([CarryOverAmnt2021]:[CarryOverAmnt2021], [2022CapEx]:[2022CapEx]) >= 250000, [2022OpEx]@row)
If this answer answers your question, please press "Yes" above  it helps the community (and those random Googlers out there 👀) find solutions like yours faster.
Love,
Brett Wyrick  Connect with me on LinkedIn.

2023 update: I'm no longer working on Smartsheet stuff. I started working at Microsoft in 2022, plus I have 1yearold twins at home and frankly, I don't have enough time to do Smartsheet anymore. It's been real, Smartsheeters!

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

=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?
If this answer answers your question, please press "Yes" above  it helps the community (and those random Googlers out there 👀) find solutions like yours faster.
Love,
Brett Wyrick  Connect with me on LinkedIn.

2023 update: I'm no longer working on Smartsheet stuff. I started working at Microsoft in 2022, plus I have 1yearold twins at home and frankly, I don't have enough time to do Smartsheet anymore. It's been real, Smartsheeters!

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

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 crosssheet formulas, you could simply use "@row" at the end of the column and it'd work. But since you're utilizing crosssheet 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?
If this answer answers your question, please press "Yes" above  it helps the community (and those random Googlers out there 👀) find solutions like yours faster.
Love,
Brett Wyrick  Connect with me on LinkedIn.

2023 update: I'm no longer working on Smartsheet stuff. I started working at Microsoft in 2022, plus I have 1yearold twins at home and frankly, I don't have enough time to do Smartsheet anymore. It's been real, Smartsheeters!

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

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.
If this answer answers your question, please press "Yes" above  it helps the community (and those random Googlers out there 👀) find solutions like yours faster.
Love,
Brett Wyrick  Connect with me on LinkedIn.

2023 update: I'm no longer working on Smartsheet stuff. I started working at Microsoft in 2022, plus I have 1yearold twins at home and frankly, I don't have enough time to do Smartsheet anymore. It's been real, Smartsheeters!

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
Help Article Resources
Categories
Check out the Formula Handbook template!