Nested Sumif Formula

LaurenB

Hello,

While we're training new employees I need to track their progress over a 2 week period using 9 different forms that are filled out at the end of each day; which used to be done on paper. There are 4 different teams that send in trainees so I thought it'd be easier to create 4 different workspaces for each team, which included identical sheets for each of the 9 forms and then a KPI sheet (first photo) which to pull all the important data into, so to view by team rather than a whole. I thought about doing it the other way where I create one big sheet and pull the data into other sheets, but that just baffled the mind. I did a pro desk call to see if I could use the work I've already done and was given the formula below to use (note I won't be needing to reference all sheets, but I do have some columns that have data in 6 out of the 9 forms). It's with the intention that when I enter in a trainee's name into the KPI sheet it pulls up their total overall numbers. Separated the formulas work fine but when I try to nest them together to find the total of, say, Total Calls Made, I get #incorrect or #inparseable. Below is my formula, where did I go wrong? 

=SUMIF({TRAINEE NAME D1}, [TRAINEE NAME]@row, {TOTAL CALLS MADE D1} + SUMIF({TRAINEE NAME D7}, [TRAINEE NAME]@row, {TOTAL CALLS MADE D7}))

I've tried it a few different ways and swear I've copied it correctly from the below formula, and can't seem to figure it out. The ones I have in already are just from one sheet, and this is trying to pull from 2. I don't even know how I'll do 6. Thanks for the help!

Andrée Starå

Hi Lauren,

In your formula, you're missing one parenthesis and have one too many in the end.

=SUMIF({TRAINEE NAME D1}, [TRAINEE NAME]@row, {TOTAL CALLS MADE D1}) <Added one parenthesis to close the first part.  + SUMIF({TRAINEE NAME D7}, [TRAINEE NAME]@row, {TOTAL CALLS MADE D7}) <Removed one parenthesis, you had two.

This is the correct one:

=SUMIF({TRAINEE NAME D1}, [TRAINEE NAME]@row, {TOTAL CALLS MADE D1}) + SUMIF({TRAINEE NAME D7}, [TRAINEE NAME]@row, {TOTAL CALLS MADE D7})

Did it work?

At a glance, I also noticed this in picture 2. You're using parenthesis at the end of each SUMIF and not the curly bracket.

Hope that helps!

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

LaurenB

Hi Andrée,

Yes it has worked! I was looking and just couldn't see the mistake so thank you so much. I'll go enter this into all my other cells now

 

Thanks!

Lauren

Andrée Starå

Excellent!

Happy to help!