Options
✭✭✭✭

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})

Tags:

• ✭✭✭✭
Options

Try this:

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

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 1-year-old twins at home and frankly, I don't have enough time to do Smartsheet anymore. It's been real, Smartsheeters!

• ✭✭✭✭
Options

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

• ✭✭✭✭
Options

=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?

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 1-year-old twins at home and frankly, I don't have enough time to do Smartsheet anymore. It's been real, Smartsheeters!

• ✭✭✭✭
Options

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

• ✭✭✭✭
edited 12/06/21
Options

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?

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 1-year-old twins at home and frankly, I don't have enough time to do Smartsheet anymore. It's been real, Smartsheeters!

• ✭✭✭✭
Options

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

• ✭✭✭✭
Options

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.

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 1-year-old twins at home and frankly, I don't have enough time to do Smartsheet anymore. It's been real, Smartsheeters!

• ✭✭✭✭
Options

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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!