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!

Quick Links

Trending discussions

Status Log Push to Dashboard
I want to push the latest status log sheet entry to the dashboard. I have added working columns for the last date, recent value (0 or 1) and recent status (text field). The recent columns are blank except for the most recent data rows (that part of the sheet is working). I have a project metric sheet template across topics…
Calculate Project Time TOTAL for PM across aligned projects
Hi - I am trying to figure out how to create a SUM of the Total Project Time aligned to each PM for all of their projects. On my master sheet I have the Resource Type, Resource Name, and a Helper Column for Time. The Helper Time is an estimated allotted time based on the timeframe of the project. Example: <3 months = 25…
How to Create Time Duration
I have a report where I need a formula to figure out time duration. For example, employee starts at 8:00 am and ends at 9:00 am, I need the next column to tell me 1.00 to indicate he worked 1 hour. I will be using military time throughout this sheet.