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

You forgot your second SUMIFS function after the minus sign.
You have
=SUMIFS(..........)  (..........)
but it should be
=SUMIFS(..........)  SUMIFS(..........)

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?

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.

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".

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