Formula Sumif Range w. Web Form

Nicole Hanik

I have a sheet where I am using a Webform to gather data.  Issue is the Sumif formula I have at the top of my sheet will not include the added line entries of the webform entries.

=SUMIF(Task2:Task4, Task5, Hours2:Hours4)

New Webform entry is line 5, Formula will not automatically capture that added line. 

Please help.  

Objective is that based on the task name to obtain a total number of hours worked (in a massive list of data)  - that is then linked back to a project summary as a total hours worked cell.  

Mike Wilday

Can you include a screenshot of your data? It's difficult to picture what you are talking about. 

To sum a complete range you would just use the column header separated by a colon... Task:Task. This would account for new rows that are added to a sheet by the web form. You just want to make sure that if you are summing particular rows that you don't do it in the same column or you'll get a circular reference error.

Nicole Hanik

Picture 1 is the roll up where the formula needs to be

Picture 2 is the web form data

 

Based on the web form data I want to sum by task name

Paul Newcome

Is this all on the same sheet?

Nicole Hanik

Yes. 

Not sure if there is a different way.  I am open to any ideas to get this accomplished

 

Mike Wilday

The Circular reference issues are because you have the sum formulas in the same column as the amounts you are summing. 

My recommendation is to switch the labels and the formulas. So the summed data will be shown in the Task column and the Labels will show in the hours' column. You could also create a separate sheet and use cross-sheet references to sum the data from the one sheet into a new one. Either would work. 

For more on smartsheet errors: 

• https://help.smartsheet.com/articles/2476176-formula-error-messages

For more on cross-sheet-formulas: 

• https://www.smartsheet.com/blog/powerful-and-flexible-cross-sheet-formulas
• https://help.smartsheet.com/articles/2476606-formulas-reference-data-from-other-sheets

You're getting the blocked message because of the errors in the ranges you are summing. 

• https://help.smartsheet.com/articles/2476176-formula-error-messages#blocked

 

Paul Newcome

Mike is correct on all points. I personally would suggest moving your metrics to a different "summary" sheet and using cross sheet reference as detailed in Mike's link above. That ensures the ability to look at entire columns without having to worry about circular references.

 

Switching the columns the metrics are in would work, and should be fine if that's where your formulas stop. I have one sheet that I created though where I referenced an entire column for some automation. By the end of the automation (it took months to build everything) I realized I needed to make some other adjustments to a few cells. I had ended up working myself back around creating the circular reference error and jacking everything up.

Nicole Hanik

The cross reference Formula was the solution I was looking for!! Thank you so much!!

Mike Wilday

Glad we could be of assistance!