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

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    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

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

Answers

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    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

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    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!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

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

    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!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Genevieve P.
    Genevieve P. Employee
    edited 09/09/21

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

  • Paul Newcome
    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".

    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!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • @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.

  • 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

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!