Formula Sumif Range w. Web Form
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.
Comments
-
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.
-
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
-
Is this all on the same sheet?
-
Yes.
Not sure if there is a different way. I am open to any ideas to get this accomplished
-
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:
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.
-
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.
-
The cross reference Formula was the solution I was looking for!! Thank you so much!!
-
Glad we could be of assistance!
Help Article Resources
Categories
Check out the Formula Handbook template!
