Counting and Averaging with Exclusions

Samantha Rice

Hello Smartsheet Community,

My organization uses Smartsheet to track our recruiting efforts. Each of our divisions has its own sheet, and we have a metrics sheet to tie all of the divisional sheets together.

I am having issues both at the divisional sheet-level and the metrics sheet-level.

Problem 1) Sheet Summary Averaging, to Exclude a Certain Crew

Each divisional sheet contains a row that calculates the number of days vacant, which is then averaged quite simply in the metrics sheet.

However, we also need an average of the number of days vacant for all crews except one. Is there an easy way to achieve this?

Not being able to think of one, we decided to SUM all number of days vacant and SUM all number of days vacant in crew 19, and subtract them. To create my own average formula, I then need to figure out how many lines with crew 19 in the (Crew) column contain a value in the (# of Days Vacant) column. Can anyone give any insight on how to write this formula?

________

Problem 2) Metrics Sheet COUNTIFS, Excluding a Certain Crew

On the metrics sheet, we have one data point that counts all lines with a status of "Open," "Closed," and "Request to Post." This formula works fine:

=COUNTIFS({Status}, "Open") + COUNTIFS({Status}, "Request to Post") + (COUNTIFS({Status}, "Closed"))

However, we also need a separate data point that counts all of these statuses NOT in a certain crew. This formula is giving me trouble, shown as "unparseable." Any advice would be helpful:

=IF(ISNOT({Crew}, "Crew 19")), COUNTIFS(({Status}, "Open") + COUNTIFS({Status}, "Request to Post") + (COUNTIFS({Status}, "Closed")))

Paul Newcome

@David Tutwiler There currently is no AVERAGIFS in Smartsheet. Only an AVERAGEIF to allow for a single set (kind of like SUMIFS vs SUMIFS). If you need extra range/criteria sets for an average, you would need to use an AVERAGE/COLLECT.

=AVERAGEIF(Crew:Crew, @cell <> "Crew 19", [# of Days Vacant]:[# of Days Vacant])

or

=AVERAGE(COLLECT([# of Days Vacant]:[# of Days Vacant], Crew:Crew, @cell <> "Crew 19")

You are correct though that you can use <> for "not equal to".

I would suggest using an OR inside of the COUNTIFS for the multiple criteria sets for the single {Status} range though.

=COUNTIFS({Status}, OR(@cell = "Open", @cell = "Request to Post", @cell = "Closed"), {Crew}, @cell <> "Crew 19")

David Tutwiler

1) I think both scenarios are similar. You could use AVERAGEIFS or SUMIFS or COUNTIFS, which give you the ability to set multiple conditions to the summary (or average or count). You would just need to take the requirements you have in your formula, and add one more condition that says the Crew is not 19. An example is below.

2) I think you could put a <> (not equal to) in the CountIfs and it would work just fine. When you continue the formula out to more than 1 If, then you're really doing an AND operation. So if you wrote something like:

=COUNTIFS({Crew}, <>"Crew 19", {Status}, "Open")

then it will count all the things that have a status of open that don't have a crew of 19. I think that will work for you.

Paul Newcome

@David Tutwiler There currently is no AVERAGIFS in Smartsheet. Only an AVERAGEIF to allow for a single set (kind of like SUMIFS vs SUMIFS). If you need extra range/criteria sets for an average, you would need to use an AVERAGE/COLLECT.

=AVERAGEIF(Crew:Crew, @cell <> "Crew 19", [# of Days Vacant]:[# of Days Vacant])

or

=AVERAGE(COLLECT([# of Days Vacant]:[# of Days Vacant], Crew:Crew, @cell <> "Crew 19")

You are correct though that you can use <> for "not equal to".

I would suggest using an OR inside of the COUNTIFS for the multiple criteria sets for the single {Status} range though.

=COUNTIFS({Status}, OR(@cell = "Open", @cell = "Request to Post", @cell = "Closed"), {Crew}, @cell <> "Crew 19")