SUMIFS Formula

Options

I am trying to create a SUMIFS formula referencing another sheet: =SUMIFS({Sheet - PTO & Project Staffing Range 2}, {Sheet - PTO & Project Staffing Range 1}, Projects@row, {Sheet - PTO & Project Staffing Range 3}, "Trainee Solicitor")

The column I am trying to add is a formula so, I have tried VALUE in front of the above formula but it isn't bringing back a value (I'm expecting 42 to come back).

If I create a SUMIF formula, excluding the last criteria for "Trainee Solicitor" it works fine so, I'm a bit stumped at what I'm doing wrong?


Thanks

Cheryl

Tags:

Best Answer

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

    Hi @Cheryl Collins

    Thank you for these screen captures, this helps a lot! The formula will be evaluating the criteria in each individual row, regardless of the rows above or below.

    This means that the correct answer to your formula is 0, since there are no rows that say "Rushmoor" AND also say "Trainee Solicitor" in the same row. The cell in your Project column is blank wherever it says "Trainee Solicitor".

    You'll want to either add a formula into these blank cells that return the Parent:

    =PARENT()

    Or you could have a helper column with a column formula applied, then hide this column in your sheet.

    =PARENT([Project]@row)

    Then adjust your first criteria range {Sheet - PTO & Project Staffing Range 1} to look at this helper column for the Parent name instead of the Project column. Does that make sense?

    Cheers,

    Genevieve

Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hi @Cheryl Collins

    Can you post a screen capture of the sheet you're looking into, identifying the Column that's your "Range 3" and column type? (But please block out sensitive data). It would also be helpful to know how this formula is not working, are you receiving an error or an incorrect result?


    If the result is incorrect, it may be that the formula cannot find a match in your Staffing Range 3 column to the exact words "Trainee Solicitor". Check to ensure that the text in that column is spelled the same way as what you have in your formula.

    Additionally, if the column of Range 3 is Multi-Select and this is one of many options, you'll want to use the HAS function:

    =SUMIFS({Sheet - PTO & Project Staffing Range 2}, {Sheet - PTO & Project Staffing Range 1}, Projects@row, {Sheet - PTO & Project Staffing Range 3}, HAS(@cell, "Trainee Solicitor"))


    Let me know if any of this has helped!

    Cheers,

    Genevieve

  • Cheryl Collins
    Cheryl Collins ✭✭✭✭✭✭
    Options

    Hi @Genevieve P

    I am getting an incorrect result, I'm expecting to get a return of 35 against the project but am getting a 0.

    I have just tried your formula including the HAS clause, as you are correct in your assumption, that range 3 is a drop down of options (Single option).

    As you can see against the project Rushmoor I'm expecting a return of 35 but am getting a zero against Trainee


    Thanks

    Cheryl

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

    Hi @Cheryl Collins

    Thank you for these screen captures, this helps a lot! The formula will be evaluating the criteria in each individual row, regardless of the rows above or below.

    This means that the correct answer to your formula is 0, since there are no rows that say "Rushmoor" AND also say "Trainee Solicitor" in the same row. The cell in your Project column is blank wherever it says "Trainee Solicitor".

    You'll want to either add a formula into these blank cells that return the Parent:

    =PARENT()

    Or you could have a helper column with a column formula applied, then hide this column in your sheet.

    =PARENT([Project]@row)

    Then adjust your first criteria range {Sheet - PTO & Project Staffing Range 1} to look at this helper column for the Parent name instead of the Project column. Does that make sense?

    Cheers,

    Genevieve

  • Cheryl Collins
    Cheryl Collins ✭✭✭✭✭✭
    Options

    Hi @Genevieve

    Aah, of course 🙈 thanks, that makes perfect sense.

    Thanks for your help.

    Cheryl

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    No problem at all! I'm glad we could figure it out. 🙂

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!