Sumifs question

JH@HL

Sumifs forumula question here. For work allocation, each week team members report on the name of their project and the approximate percentage of time it will take. For example, Project A = 50% and Project B = 20%. I'm trying to sum the total of the projects. The first criteria is the status of the project; I want to count only "in progress" precentages. The second criteria is by contact. Since the sheet displays multiple team members, i want a cell to count only the percentages for that team member.

Two questions: first, i have been able to get the formula to work, but i didn't save it properly, and now i can't get back to that original formula. Second, usually when I do this kind of thing, I figure out the formula for the first team member, and then copy and paste the formula for all of the remaining team members, and simply change the contact name. However, on this sheet when i do that, when i make the copy, the original one then shows "unparseable"

Final note* this sheet is for a colleague in London so the word spelling for the word "utilisation" is different than it is spelled in the US. That's not the error.

Genevieve P.

Hi @JH@HL

It looks like you've got the structure of the formula down pat, which is great! However I see that you're pasting this formula into the actual Forecasted column. This may be why you're getting an error as soon as you add two of them.

I would suggest either using a separate helper column to create your calculations per-employee (perhaps one column with their name, and the one next to it with their totals, like a little chart to the side?), or if you have the ability, use a Sheet Summary field to paste your formulas (see here).

If you chose to do two helper columns, you could reference the name in the cell next to the formula, meaning you'd only have to write it once and then you can copy/paste the exact same formula. (Let me know if you want to see a pictured example of this).

The other thing I notice is that your column names are not lighting up with different colours in the formula, which indicates that it can't find one of your columns and will return "Unparseable".

Looking at your Forecasted column reference, you have spelled "utilisation" with an S for the first instance, but then with a z for the second instance. If you change that z to an s you should see all the columns light up in different colours! Should this not work, I would recommend deleting the column references and clicking on a cell in the column to bring in the column name (instead of typing it out manually).

Let me know if this works for you!

Cheers,

Genevieve

Paul Newcome

I agree with both of @Genevieve P's observations.

1. The location of your formula is going to end up throwing an error if it is replicated within the same column.
2. The spelling of Utilisation vs Ulilization.

Depending on your setup and needs though, you may be able to make some very minor tweaks to your layout and leverage the CHILDREN() function to keep the formula in the same column.

Genevieve P.

Hi @JH@HL

It looks like you've got the structure of the formula down pat, which is great! However I see that you're pasting this formula into the actual Forecasted column. This may be why you're getting an error as soon as you add two of them.

I would suggest either using a separate helper column to create your calculations per-employee (perhaps one column with their name, and the one next to it with their totals, like a little chart to the side?), or if you have the ability, use a Sheet Summary field to paste your formulas (see here).

If you chose to do two helper columns, you could reference the name in the cell next to the formula, meaning you'd only have to write it once and then you can copy/paste the exact same formula. (Let me know if you want to see a pictured example of this).

The other thing I notice is that your column names are not lighting up with different colours in the formula, which indicates that it can't find one of your columns and will return "Unparseable".

Looking at your Forecasted column reference, you have spelled "utilisation" with an S for the first instance, but then with a z for the second instance. If you change that z to an s you should see all the columns light up in different colours! Should this not work, I would recommend deleting the column references and clicking on a cell in the column to bring in the column name (instead of typing it out manually).

Let me know if this works for you!

Cheers,

Genevieve

Paul Newcome

I agree with both of @Genevieve P's observations.

1. The location of your formula is going to end up throwing an error if it is replicated within the same column.
2. The spelling of Utilisation vs Ulilization.

Depending on your setup and needs though, you may be able to make some very minor tweaks to your layout and leverage the CHILDREN() function to keep the formula in the same column.

JH@HL

Hello, Genevieve and Paul - a bit of a belated response, but a huge THANK YOU for the winning suggestions! It totally worked to use the Sheet Summary feature. Now I have a new tool in my toolbox! Your responses were so helpful because they were so quick, solved the problem, and helped me learn something new. All of this is great because I'm trying to get my teams away from Excel and onto Smartsheet. The more I can replicate the familiar format, the more convincing the switch. I've been able to move several, but this particular format was tricky for me. Thanks a million!

Julia

Genevieve P.

Hi Julia,

That's great to hear! Let us know if you have any other formula questions 🙂

Paul Newcome

Happy to help! 👍️