SUMIFS problem

Options

I want to sum a set of data using the following criteria:

If project Status is "submitted" and Division is "Pharma" I want to sum Est Savings. Here is what I have so far:

=SUMIFS({Idea Capture (with Form) Range 1}, [Primary Column]@row, {Idea Capture (with Form) Range 4}, {Idea Capture (with Form) Range 2})

Best Answer

  • Mike TV
    Mike TV ✭✭✭✭✭✭
    Answer ✓
    Options

    @JimTod

    =SUMIFS({test data Sheet Range 1}, {test data Sheet Range 2}, ="Submitted", {test data Sheet Range 3}, ="Pharma")

    This formula above should work for you. It looks like there were 2 errors you had with your formula. The first is after {test data Sheet Range 3} it looks like you have a period instead of a comma. Second error is you have a comma after "Pharma" that shouldn't be there. Change the period to a comma and remove that comma after "Pharma" and it should work if your ranges are set correctly.

Answers

  • Mike TV
    Mike TV ✭✭✭✭✭✭
    Options

    @JimTod

    =SUMIFS({Range to Sum}, {Range to find Submitted}, ="Submitted", {Range to find Pharma}, ="Pharma")

    I'm not sure exactly what your ranges should be due to your formula being mixed up but this should get you in the right direction. If you can't figure it out, please give me more info on your ranges.

  • JimTod
    Options

    Here is a screenshot of my data:

    here is the SUMIFS formula I am trying:

    =SUMIFS({test data Sheet Range 1}, {test data Sheet Range 2}, ="Submitted",{test data Sheet Range 3}.="Pharma",)

    I am trying to sum the Est Savings if the status is Submitted and it is in the Pharma division

  • Mike TV
    Mike TV ✭✭✭✭✭✭
    Answer ✓
    Options

    @JimTod

    =SUMIFS({test data Sheet Range 1}, {test data Sheet Range 2}, ="Submitted", {test data Sheet Range 3}, ="Pharma")

    This formula above should work for you. It looks like there were 2 errors you had with your formula. The first is after {test data Sheet Range 3} it looks like you have a period instead of a comma. Second error is you have a comma after "Pharma" that shouldn't be there. Change the period to a comma and remove that comma after "Pharma" and it should work if your ranges are set correctly.

  • JimTod
    Options

    Thanks for all your help. It worked. It was driving me a bit mad looking at it

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!