A subtraction
I am getting an error message #ParSEABLE with the formula below, the values are coming from the below link as you can see in the second snapshot
Best Answer
-
It looks like you need to separate SUMIF statements, then minus one from the other.
For example:
=SUMIF(formula) - SUMIF(formula)
Try this:
=SUMIF({Actual Billing Job}, [Job/ Opp#]@row, {Actual Billing (Total Billings) Jan}) - SUMIF({Forecasted Billing(Cashflow) Job}, [Job/ Opp#]@row, {Forecasted Billing(Cashflow) Jan})
Let me know if this works for you!
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
Answers
-
It looks like you need to separate SUMIF statements, then minus one from the other.
For example:
=SUMIF(formula) - SUMIF(formula)
Try this:
=SUMIF({Actual Billing Job}, [Job/ Opp#]@row, {Actual Billing (Total Billings) Jan}) - SUMIF({Forecasted Billing(Cashflow) Job}, [Job/ Opp#]@row, {Forecasted Billing(Cashflow) Jan})
Let me know if this works for you!
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
You forgot your second SUMIFS function after the minus sign.
You have
=SUMIFS(..........) - (..........)
but it should be
=SUMIFS(..........) - SUMIFS(..........)
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Thank you @Genevieve P. and @Paul Newcome that worked.
IF I can aks for help on one more thing relating to the same project. My formula below is getting the same #Unparseable
-
@Genevieve P. Typing at the same time again. Hahaha.
@Shannon Lacerda Smartsheet does not have an IFS function. Are you able to describe exactly what you are wanting to accomplish with the formula?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
It's always validating to answer the same as you would! Confirmation I'm on the right track.
I agree, it would be helpful to know in writing what it is you want the formula to do.
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
thank you all for the quick responses @Shannon Hallberg @Genevieve P. @Paul Newcome
The purpose of the second formula to find the % variance of a Forecasted Billing and the Actual billing
-
@Shannon Hallberg @Genevieve P. @Paul Newcome Here is a better way to look at what I need
Below is the formula that I have done that work and it is by CELL, I want to change this and select a columns, and distinguish a certain cell as the main dominator.
=IF({4557 jan} > {Actual Gantt 4557 jan}, SUM({Actual Gantt 4557 jan} / {4557 jan}), SUM({4557 jan} / {Actual Gantt 4557 jan}))
I came up with the below but this is not working either
=IF({Forecasted Billing(Cashflow) Job}, [Job/ Opp #]@row, {Forecasted Billing(Cashflow) Jan} > {Actual Billing Job}, [Job/ Opp #]@row, {Actual Billing (Total Billings) Jan}, SUM({Actual Billing Job}, [Job/ Opp #]@row, {Actual Billing (Total Billings) Jan} / {Forecasted Billing(Cashflow) Job}, [Job/ Opp #]@row, {Forecasted Billing(Cashflow) Jan}), SUM({Forecasted Billing(Cashflow) Job}, [Job/ Opp #]@row, {Forecasted Billing(Cashflow) Jan} / {Actual Billing Job}, [Job/ Opp #]@row, {Actual Billing (Total Billings) Jan}))
-
What exactly are you referencing and what exactly are you trying to accomplish? Are you able to write out the logic in more of a sentence structure as opposed to a formula such as
I want to take this date and compare it to that date. If this date is greater than that date then sum everything in [Column A] if [Column B] equals "specific text".
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
=IF({4557 jan} > {Actual Gantt 4557 jan}, SUM({Actual Gantt 4557 jan} / {4557 jan}), SUM({4557 jan} / {Actual Gantt 4557 jan}))
I am looking to find the %, but if "4557 Jan" is greater than "Actual Gantt 4557 Jan" then is needs to divide in the reverse.
-
I would suggest having this formula in the Source sheet, versus creating a cross-sheet reference.
For example, in a source sheet you could change each of your {column references} to be a cell reference in the row, which would then output a specific number.
Ex:
=IF([Jan]@row > [Actual Gantt Jan]@row, [Actual Gantt Jan]@row / [Jan]@row, [Jan]@row / [Actual Gantt Jan]@row)
That will give you the percent Per Row in the source sheet, and you could make it a column formula so it goes through your whole sheet.
Then if you wanted to display this percent in your current sheet, you could use an INDEX(MATCH or an INDEX(COLLECT to bring back the percent based on a unique identifier for the rows that match across the sheets. Does that make sense?
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 379 Global Discussions
- 213 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 450 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!