I'm trying to calculate total cost of multiple criteria and receive the #unparseable.

Options

I need to pull total cost savings that are within a specific quarter. In one sheet I have captured a column that has a dropdown lists Q1, Q2, Q3, Q4 where each row shows the savings for that particular line item. I now need to capture total sum of cost savings within each quarter.

Best Answers

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Options

    Hey @CrystalA

    A copy of your starting formula would be helpful. Is the formula in the same sheet as the data, or is the data in a different sheet.

    Assuming same sheet

    =SUMIFS([cost savings column]:[cost savings column], [drop down column]:[drop down column], [dropdown column]@row)


    If the data is in a different sheet:

    =SUMIFS({cost savings column from source sheet}, {drop down column from source sheet}, [dropdown column]@row)

    *In either formula, instead of [dropdown column]@row you can substitute the specific quarter designation using the abbreviation enclosed in double quotes. ex. "Q1"

    will this work for you?

    Kelly

  • CrystalA
    Options

    Hi Kelly, thank you for getting back. The formula I started off with is below. The data being pulled is in a different sheet. I tried the one you had captured and I still get the #UNPARSEABLE.

    I'm needing to calculate what the cost savings would be for the timing column, grouping each quarter. If I could get the formula for Q1 then I can apply the same for the remaining quarters to track on a dashboard. Thank you for your help.


    =SUMIFS({FY24 Global HR Ops HC Change Report Range 5}, {FY24 Global HR Ops HC Change Report Range 4}, "Q1"@row)


  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓
    Options

    Hi @CrystalA

    The @row reference is if you're looking for a value in a cell. It looks like you just want to find "Q1" and have typed that into your formula. If so, then there's no need for @row!

    Try this:

    =SUMIFS({FY24 Global HR Ops HC Change Report Range 5}, {FY24 Global HR Ops HC Change Report Range 4}, "Q1")

    Cheers,

    Genevieve

  • CrystalA
    CrystalA ✭✭
    Answer ✓
    Options

    That worked, thank you!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!