% Rool-Upon another sheet

I have created a construction schedule where i have many lines of of tasks that apply in a mixed format to different subcontractors. Each task has it own % value. I have created another sheet where I have listed the Subcontractors and am trying to roll up the %of every line (associated with a specific subcontractor into a single % on this sheet. I have started off to with using the formula =SUM(COLLECT) and that seems to work, But i need the average not the sum. I have tried =AVG(COLLECT), but with no luck. Maybe I am not using the AVG correctly?

Any ideas?

Find more posts tagged with

Construction

Community Platform

Project Management

Account and User Management

Comments

Paul Newcome

What is the actual AVG(COLLECT()) formula you are trying to use?

Erik Johnson, PE

=AVG({Sheet - CF ES Timeline Range 2}COLLECT({Sheet - CF ES Timeline Range 4}, {Sheet - CF ES Timeline Range 1}, Vendors1))

Erik Johnson, PE

This is what my formula looks like and i have no idea if it is right. Diving into new territory here

=AVG({Sheet - CF ES Timeline Range 2}*countifs({Sheet - CF ES Timeline Range 2})COLLECT({Sheet - CF ES Timeline Range 4}, {Sheet - CF ES Timeline Range 1}, Vendors1))

Paul Newcome

Are you getting an error?

Erik Johnson, PE

it returns #UNPARSEABLE.

It seems to work fine with SUM but not AVG

Paul Newcome

Try using =AVG(COLLECT({Range of numbers to be averaged}, {Criteria Range 1}, Criteria 1, {Criteria Range 2}, Criteria 2))

Erik Johnson, PE

I tried that and got #INCORRECT ARGUMENT SET...

=AVG(COLLECT({Sheet - CF ES Timeline Range 2}, {Sheet - CF ES Timeline Range 3,Vendors1}))

Erik Johnson, PE

Paul I re-did it and got it to work thanks for all your help!

Paul Newcome

Excellent. Happy to help.

Sign in to join the conversation!

Quick Links

Trending discussions

Why are my answers not showing up when a document is generated?
I have my Smartsheet set to generate a document when a form is filled out. The document is a fillable PDF and when the document generates not all of the answers populate on the generated PDF. I have tried making the PDF again from scratch, I have tried copying and pasting the fields so I know they're the exact same…
Giving someone access to view a report, but not the source sheet
I need to be able to pull data manually from the source sheet into a report to send to another person for approval. I do not want to give the person approving access to view other sensitive information on the source sheet. Is this possible?
Time Zone / real time stamp
hello, How can I set our account to reflect real time and dates. for example, when viewing the cell history, It usually shows the day before. The correct time stamp for this edit should be 9/3/25 5:50pm. Any way to change this to our correct time zone? We are in Guam.