Using Collect() Function to Summarize Data by Project and Date

emailmike1994

I have two sheets, one where I had a form that lets people input safety scores a row at a time for a given construction job site and another where I'm looking to summarize all the safety scores for a particular job site overall, today, this week, etc.

I can get the overall safety score for a particular job site:

=IFERROR(AVG(COLLECT({Scores}, {Jobsite}, "HUBHAR")), "")

I can also get the overall safety score across all job sites for today with the below:

=IFERROR(AVG(COLLECT({Scores}, {Dates}, TODAY())), "")

It's when I try to just pull data for today for one specific job site that I run into problems.

I've gathered that I would want to use the COLLECT formula and I was thinking I wanted to add a second range and criteria to get the date piece of things factored in. 

But when I try to combine those two above approaches, I fall flat. I'm not sure if I'm just not separating the arguments properly or mixing data types or what, but it won't work.

Any help would be greatly appreciated. I will also need to repeat for week and year, so any guidance there would also be great.

Thanks!

Paul Newcome

Can you provide some screenshots or post what you are using for the combined formula?

emailmike1994

Thanks for the response, Paul. I don't have the exact code I used because I took it out when it wasn't working, but I basically just tried to add a second set of parameters to the COLLECT function as per the specification (https://help.smartsheet.com/function/collect).

I was under the impression it would return something if all the sets of parameters returned something.

Not sure if I'm maybe inputting this wrong. I tried with brackets and with semicolons because they're both on that specs page (even though I suspect it may be wrong).  Still nothing.

=IFERROR(AVG(COLLECT({Scores}, {Jobsite}, "HUBHAR", {Scores}, {Dates}, TODAY())), "")

Any thoughts?

Paul Newcome

=IFERROR(AVG(COLLECT({Scores}, {Jobsite}, "HUBHAR", {Scores}, {Dates}, TODAY())), "")

 

Are your ranges all from the same sheet, or do you have two sheets that both have a column named Scores?

 

If both {Scores} are the same range from the same sheet then you can remove the second {Scores}. When adding in addition criteria it is just that. The range that will actually be collected ({Scores}) has already been specified when you put it in the COLLECT function as the first entry. The below is an example. The COLLECT function is saying to collect all of the Scores where Jobsite = Hubhar AND Dates = Today. If it is NOT Hudhar AND Today, the score will not be collected.

 

=IFERROR(AVG(COLLECT({Scores}, {Jobsite}, "HUBHAR", {Dates}, TODAY())), "")

 

If it is two separate sets of scores, you will need something along the lines of...

 

=IFERROR(AVG(COLLECT({Scores}, {Jobsite}, "HUBHAR"), COLLECT({Scores}, {Dates}, TODAY())), "")

 

What this does is pulls the value of the two separate COLLECT functions and then averages them together. If you wanted to average each set of scores first and then average the averages you would use

 

=IFERROR(AVG(AVG(COLLECT({Scores}, {Jobsite}, "HUBHAR")), AVG(COLLECT({Scores}, {Dates}, TODAY()))), "")

 

I hope this helps. I know there are a few possibilities here, but without knowing EXACTLY what you have going on, I figured it would be better to give a range of options hoping that one of them fits your actual situation.

 

Let me know if one of these works for you. If none of them work, maybe I can get some more detail from you to try to help.

emailmike1994

This is exactly what I wanted and needed. Thank you so much. I was pulling from the same range of scores on a separate sheet. Sorry to make you do all that guesswork, but this is perfect.

Paul Newcome

No apologies needed. Happy to help.

LGustin

I'm having trouble using the join and collect with two sets of criteria. Can you help? I also want to know if instead of the delimiter being a semicolon and a space, if it can be a "semicolon and ctl+enter" to move the following text to the next line, but still in the same cell.

 

Here's the formula with one set of criteria: 

=JOIN(COLLECT({Product Roadmap full name Range 1}, {Product Roadmap Release Month Range 1}, Release@row), "; ")

 

Formula I've tried with two criteria that has a result of #UNPARSEABLE:

=JOIN(COLLECT({Product Roadmap full name Range 1}, {Product Roadmap Release Month Range 1}, Release@row, [{Product Roadmap Lane Range 1},Lane@row]), "; ")