SUMIF - Circular Reference?

jrw

Hello all! 



I'm pretty sure I saw this functionality in one of my Engage sessions, so I'm crossing my fingers that there's a simple fix! 

I'd like to create a form that would allow individuals to submit project-related expenses for approval. I envision having a list of project numbers at the top of the sheet (see image, with sample projects "10" through "40"), with requests coming through the form populating the bottom of the sheet. Ideally, Smartsheet would look for all expenses associated with project #10 and sum them... and then do the same for project #20, project #30, etc.... 



I can get this to work for the first project, but as soon as I try to use a similar formula for the second project, I get a "circular reference" error. Is there something I'm missing here? And if I'm not approaching this the right way, is there another way to accomplish this? (Important note... there will be 100+ projects on a single sheet, not just 4 or 5, so it's got to work for a large number of project numbers existing on one sheet.)

THANK YOU!!!! 

SmSulli

jrw

You are super close!  You do not need the = sign in your criteria.  You'll want to set a defined set of rows for the ranges.  Here is the formula I would suggest.  I've added $ to help when copying the formula.

=SUMIF($[Column7]$6:$[Column7]$9, [Project Number]1, $Amount$6:$Amount$9)

Shawn

 

jrw

Shawn,

For some reason I can't click the "Reply" button (I'm in Safari, if that matters....?) so I'm hoping you see this! 



THANK YOU! My formula is now working properly!!! I truly appreciate the help. 

Jody

SmSulli

You're Welcome!