Using Contact List data in IF statement in Sheet Summary

wdpassport

I'm working on a system of sheets to track requisitions within our Talent Acquisition team. I've built out a helper sheet designed to help us track the average requisition priority ranking that each recruiter has. Since this is a helper sheet, its only purpose is to gather this metric, so I don't care what the sheet looks like and can add any columns or formulas to get the info I need. I'm currently working in a Sheet Summary, but I can

The recruiter information is populated from another sheet and comes in the form of a Contact List data type. I'm trying to write a formula that takes the average priority ranking (0-5) for each row but only if it is assigned to a certain recruiter.

I'm struggling to get an IF statement to recognize the contact information as TRUE in the Sheet Summary. I've tried simple IF, nested HAS, nested FIND, and nothing seems to be working for me.

The way I think it should work, the formula would look like this:

=AVG(IF([Recruiter]@row = "NAME", [Priority Rank]:[Priority Rank])

By looking at it, I can see why it wouldn't work as I'm needing the IF statement to look at an individual row value and the AVG statement to look at a series of data within a column.

Is there a workaround to take the average but only if a certain recruiter is listed?

If it makes it easier, I'm trying to populate that data into a dashboard, so maybe there's a way to do it there?

I'll add that another idea I had was to create a helper helper sheet that used a conditional automation to copy the priority ranking to another sheet for the specific recruiter and the just doing an easy AVG formula in that sheet. That would work, but it feels like a lot to make a whole separate sheet for each recruiter just to isolate the priority number.

Kelly Moore

Hello @wdpassport

You can use AVERAGEIF as you currently only have one criteria that you are filtering against. My personal preference is to use AVG(COLLECT()), regardless if I only have criteria, in case I need to add additional criteria in the future. Since the syntax between the two formulas are different, I prefer to use the structure that I wouldn't have to rebuild in the future. Again, it's my personal preference. I'll give you both formulas.

=AVERAGEIF(Recruiter:Recruiter, "Name", [Priority Rank]:[Priority Rank])

=AVG(COLLECT([Priority Rank]:[Priority Rank], Recruiter:Recruiter, "Name")

With the AVG/COLLECT you would add any additional criteria at the end, always with the syntax of a criteria range, criteria pair.

Kelly

wdpassport

Kelly Moore

Hello @wdpassport

You can use AVERAGEIF as you currently only have one criteria that you are filtering against. My personal preference is to use AVG(COLLECT()), regardless if I only have criteria, in case I need to add additional criteria in the future. Since the syntax between the two formulas are different, I prefer to use the structure that I wouldn't have to rebuild in the future. Again, it's my personal preference. I'll give you both formulas.

=AVERAGEIF(Recruiter:Recruiter, "Name", [Priority Rank]:[Priority Rank])

=AVG(COLLECT([Priority Rank]:[Priority Rank], Recruiter:Recruiter, "Name")

With the AVG/COLLECT you would add any additional criteria at the end, always with the syntax of a criteria range, criteria pair.

Kelly

wdpassport

@Kelly Moore Thank you so much!! I'm pretty new to SmartSheet and I'm learning newer and better ways to use the platform everyday! Learning the COLLECT syntax will help me in several other metrics we are wanting to track!