Sumifs?

Options
Emily T.
Emily T. ✭✭✭✭
edited 07/07/22 in Formulas and Functions

Hi,

From another smartsheet I am trying to pull the total % of time spent by individual project managers on various projects that are in progress. I thought it would be a sumifs formula but it's giving me an answer of "0" instead of the percentage. Below, I have it first pulling the % column, then the Project Manager column, then Program Manager column, and finally, the column status.

=SUMIFS({cPMO Dashboard Range 4}, {cPMO Dashboard Range 3}, ="name of person", {cPMO Dashboard Range 1}, ="name of person", {cPMO Dashboard Range 2}, ="In progress")

Help please!

Thank you,

Emily

Tags:

Best Answers

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

    Hi @Emily T.

    Can I clarify, are you looking for when the name appears in both cells of the same row, or are you looking for if the name is either in Range 3 OR in Range 1?

    The SUMIFS and COUNTIFS functions are looking for a row where all 3 criteria are met in the same row. This means your name would need to be in 2 cells of the same row and that row would need to be in Progress. Think of the ranges and criteria as a filter on your sheet with AND between each statement. Do you have rows that meet all 3 of those criteria?

    If you're looking for either or, then you can add (with +) two SUMIFS together:

    =SUMIFS({cPMO Dashboard Range 4}, {cPMO Dashboard Range 1}, "name of person", {cPMO Dashboard Range 2}, "In progress") + SUMIFS({cPMO Dashboard Range 4}, {cPMO Dashboard Range 3}, "name of person", {cPMO Dashboard Range 2}, "In progress")

    If this still hasn't helped, would you be able to post a screen capture of your source sheet? But please block out sensitive data.

    Cheers,

    Genevieve

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

    No problem!

    If you want to subtract the duplicates, you could actually go back to your original formula and add that at the end... for example:

    =(SUMIFS(first column) + SUMIFS(second column)) - SUMIFS(both options)

    or

    =(SUMIFS({cPMO Dashboard Range 4}, {cPMO Dashboard Range 1}, "name of person", {cPMO Dashboard Range 2}, "In progress") + SUMIFS({cPMO Dashboard Range 4}, {cPMO Dashboard Range 3}, "name of person", {cPMO Dashboard Range 2}, "In progress")) - SUMIFS({cPMO Dashboard Range 4}, {cPMO Dashboard Range 3}, "name of person", {cPMO Dashboard Range 1}, =name of person", {cPMO Dashboard Range 2}, "In progress")

    Cheers,

    Genevieve

Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    edited 07/01/22
    Options

    Hi @Emily T.

    Since you're getting 0, this shows that the formula is working correctly, it just isn't finding a match for your criteria.

    Can you try using a COUNTIFS to see how many rows it finds in your other sheet?

    =COUNTIFS({cPMO Dashboard Range 3}, ="name of person", {cPMO Dashboard Range 1}, ="name of person", {cPMO Dashboard Range 2}, ="In progress")

    If this COUNT formula is also 0, then I would check each of your criteria... are the names spelled correctly? What about "In Progress", is it possible that the other sheet has different wording?

    Then if that hasn't worked, can you identify if any of the columns you're looking into are multi-select? Your current formula is looking for an exact match, so it will only find a row if that one value is selected in the cell.

    Let me know what you find out and I'll be happy to help further!

    Cheers,

    Genevieve

  • Emily T.
    Emily T. ✭✭✭✭
    Options

    Hi Genevieve,

    Thank you for your help. The spellings are correct and if I do individual equations for each column separately, the countifs will work, but when I put all 3 together in a formula, such as above, it doesn't. I'm trying to get it to pull my name and it's the only one in those cells. Thoughts?

    Thank you!

    =COUNTIFS({cPMO Dashboard Range 3}, ="Emily", {cPMO Dashboard Range 1}, ="Emily", {cPMO Dashboard Range 2}, ="In Progress")

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

    Hi @Emily T.

    Can I clarify, are you looking for when the name appears in both cells of the same row, or are you looking for if the name is either in Range 3 OR in Range 1?

    The SUMIFS and COUNTIFS functions are looking for a row where all 3 criteria are met in the same row. This means your name would need to be in 2 cells of the same row and that row would need to be in Progress. Think of the ranges and criteria as a filter on your sheet with AND between each statement. Do you have rows that meet all 3 of those criteria?

    If you're looking for either or, then you can add (with +) two SUMIFS together:

    =SUMIFS({cPMO Dashboard Range 4}, {cPMO Dashboard Range 1}, "name of person", {cPMO Dashboard Range 2}, "In progress") + SUMIFS({cPMO Dashboard Range 4}, {cPMO Dashboard Range 3}, "name of person", {cPMO Dashboard Range 2}, "In progress")

    If this still hasn't helped, would you be able to post a screen capture of your source sheet? But please block out sensitive data.

    Cheers,

    Genevieve

  • Emily T.
    Emily T. ✭✭✭✭
    Options

    That worked! Thank you! Appreciate your insight.

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Ah wonderful! I'm glad we could identify the issue.

    One thing to note here is that if you do have any rows that have the same name in both cells, you'll be counting that row twice. Will that ever happen?

  • Emily T.
    Emily T. ✭✭✭✭
    Options

    Occasionally it will happen, but it is something I can work around for now. Thank you!

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

    No problem!

    If you want to subtract the duplicates, you could actually go back to your original formula and add that at the end... for example:

    =(SUMIFS(first column) + SUMIFS(second column)) - SUMIFS(both options)

    or

    =(SUMIFS({cPMO Dashboard Range 4}, {cPMO Dashboard Range 1}, "name of person", {cPMO Dashboard Range 2}, "In progress") + SUMIFS({cPMO Dashboard Range 4}, {cPMO Dashboard Range 3}, "name of person", {cPMO Dashboard Range 2}, "In progress")) - SUMIFS({cPMO Dashboard Range 4}, {cPMO Dashboard Range 3}, "name of person", {cPMO Dashboard Range 1}, =name of person", {cPMO Dashboard Range 2}, "In progress")

    Cheers,

    Genevieve

  • Emily T.
    Emily T. ✭✭✭✭
    Options

    This is great! Thank you!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!