A subtraction

Shannon Lacerda

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

Genevieve P.

Hi @Shannon Lacerda

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

Genevieve P.

Hi @Shannon Lacerda

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

Paul Newcome

You forgot your second SUMIFS function after the minus sign.

You have

=SUMIFS(..........) - (..........)

but it should be

=SUMIFS(..........) - SUMIFS(..........)

Shannon Lacerda

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

Paul Newcome

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

Genevieve P.

https://community.smartsheet.com/discussion/comment/304444#Comment_304444

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.

Shannon Lacerda

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 Lacerda

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

Paul Newcome

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

Shannon Lacerda

@Paul Newcome

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

Genevieve P.

https://community.smartsheet.com/discussion/comment/304507#Comment_304507

Hi @Shannon Lacerda

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