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