SUMIFS Formula

Options

Hi Team!

I am trying to work out a formula to sum the forecast amount column for a particular salesperson if the sales stage is "1 - Open" or "2 - Bid Submitted". I have tried SUMIFS formula but keep getting the inparseable error.

Please help!


Best Answer

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓
    Options

    Try this

    =SUMIFS({Sheet - Sales Pipeline Range 2}, {Sheet - Sales Pipeline Range 1}, OR(@cell = "1 - Open", @cell = "2 - Bid Submitted"), {Sheet - Sales Pipeline VPC Range 1}, "Joe Prideaux")

Answers

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

    Hey @Clare C

    What formula were you using?

    Here is one that hard codes the salesperson's name. Depending on how your data is arranged, you may be able to collect this dynamically. This formula assumes the SUMIFS formula is on the same sheet as the data. If the formula is on a different sheet, the data will use cross sheet references, - we would need to change the formula for cross references.

    =SUMIFS([Forecast Amount]:[Forecast Amount], [Sales Stage]:[Sales Stage], OR(@cell="1 - Open", @cell= "2 - Bid Submitted"), Salesperson:Salesperson, "Joe Prideaux")

    Does this work for you?

    Kelly

  • Clare C
    Options

    Hi Kelly,

    I am needing to reference another sheet so I have changed it in the formula you provided above and now it's giving me an invalid operation error.

    =SUMIFS({Sheet - Sales Pipeline Range 2}, {Sheet - Sales Pipeline Range 1}, OR({Sheet - Sales Pipeline Range 1} = "1 - Open", {Sheet - Sales Pipeline Range 1} = "2 - Bid Submitted"), {Sheet - Sales Pipeline VPC Range 1}, "Joe Prideaux")

    Thanks for your help.

    Clare

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓
    Options

    Try this

    =SUMIFS({Sheet - Sales Pipeline Range 2}, {Sheet - Sales Pipeline Range 1}, OR(@cell = "1 - Open", @cell = "2 - Bid Submitted"), {Sheet - Sales Pipeline VPC Range 1}, "Joe Prideaux")

  • Clare C
    Options

    Oh my god thank you so much!!!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!