Counting and Averaging with Exclusions

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")))

Best Answer


  • David Tutwiler
    David Tutwiler Overachievers Alumni

    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.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!