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.

Find more posts tagged with

Construction

Formulas

Support

IT & Operations

Project Management

Comments

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:

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!

Sign in to join the conversation!

Trending discussions

Lookup Value Issues
I am trying to populate a column from Sheet A into Sheet B and Sheet C. My Lookup column for Sheet A is formatted as such so there is a ' in front to account for ones that have a leading 0 and some do not. Each ID should be 6 digits long. I made a helper column looking at this Dealer ID column to try and get rid of that…
Separating Data into Multiple Rows
am I able to separate this into multiple rows? in a new sheet? Desired Result -
Index Distinct Support
I would like to parse out this data by creating a listing with a row for each person, for each project they appear on, for each month. I think this would only give me the groupings anyways, but this doesnt seem to even get me started - it popped up then people have disappeared.