Sumifs?

Emily T.

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

Genevieve P.

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.

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

Genevieve P.

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.

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.

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.

That worked! Thank you! Appreciate your insight.

Genevieve P.

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.

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

Genevieve P.

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.

This is great! Thank you!