# Nested Sumif Formula

edited 12/09/19

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!

Tags:

• ✭✭✭✭✭✭

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

SMARTSHEET EXPERT CONSULTANT & PARTNER

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

W: www.workbold.com | E:[email protected] | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• 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

• ✭✭✭✭✭✭

Excellent!

Happy to help!

SMARTSHEET EXPERT CONSULTANT & PARTNER

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

W: www.workbold.com | E:[email protected] | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!