count if formula help

Was hoping to get some tips on the following use case.

I set up a Smartsheet to track all my organization's training accreditations. This includes (student, assigned coach, product course, accreditation level, status, etc.).

I want to create a formula that counts the number of open engagements per coach. This will help me do a better job of spreading the load more evenly across all coaches as new engagements are created. I had no problem using a count if formula to track the number of times a coach's name is listed in a column, but how do I get it to ONLY count occurrences of coaches names in a column for open/active engagements and not closed?

Also, should I create a separate SmartSheet to track this or do it within the same SmartSheet?

Thanks for any tips,

Patrick

Find more posts tagged with

Formulas

Support

IT & Operations

Cell linking

Comments

steven.reed14881

Patrick,

This can be done pretty simply by using the COUNTIFS function. Below is the help text on how to do it.

Sample Usage

COUNTIFS(Quantity:Quantity, >25, [Item Name]:[Item Name], "T-Shirt")

Syntax

COUNTIFS(range1, criterion1, [range2, criterion2 ...])

range1: The group of cells to count.
criterion1: The value that determines which cells in the range will be counted; for example: 15, "Hello World!", or >25.
range2, criterion2... [optional]: Additional groups of cells and their criteria.

And I would say if this is the only metric that you are looking to pull from your sheet, just do it in a cell on your sheet. If you plan on deriving a variety of metrics, you might be better off on another sheet.

Steve

Paul Newcome

I would suggest a separate sheet. It makes it easier when you want to reference entire columns if you don't have to worry about circular references. Try setting up a table like below.

Coach Count

John F

Mike F

Steve F

In place of the "F", use this:

=COUNTIFS({Master Sheet Coach Column}, Coach@row, {Master Sheet Status Column}, OR(@cell = "Open", @cell = "Active"))

{Master Sheet Coach Column}: Use the appropriate steps to reference another sheet and select the column that holds the Coaches' names.

Coach@row: Leave as is. Looks at the name of the Coach for whatever row the formula is in.

{Master Sheet Status Column}: Use the appropriate steps to reference another sheet and select the column that holds the Status.

OR(@cell = "Open", @cell = "Active")): Leave as is. Specifies which status types to count.

patrick_daniels

Excellent, thanks Steve! I figured it out.

My only issue now is that when users input data or make changes to their accreditations (rows) it appears they can also edit or even delete the formula. Yikes. Is there not a way to lock formulas for users but make it to where they are still allowed to input/edit data in a row?

patrick_daniels

Thanks Paul! You may have seen my reply to Steve above. Do you have ideas on how to prevent users from editing or deleting formulas? I realize I can lock columns and rows, but I still need my users to input/edit data on rows but NOT to edit/delete formulas I've created.

Paul Newcome

The best suggestion I can make is to lock the column. If there is no need for anyone to edit that particular cell in a row, and it is the same cell in every row then locking the column should do the trick.

I had to share some people as admin to a workspace for various reasons. As a result they are able to edit even locked rows and columns. To help with that, I drilled it into them that if they see a lock anywhere... LEAVE IT ALONE.

I built in some conditional formatting to immediately bring it to their attention that they accidentally edited a locked cell and they really need to hit the Undo button.

I then set up some alerts and actions to notify me if they happen to save it after deleting or editing a formula.

I also save a copy of every sheet as a template in a folder they do not have access to so I can easily pull from there to fix it.

Sign in to join the conversation!

Quick Links

Trending discussions

Status Log Push to Dashboard
I want to push the latest status log sheet entry to the dashboard. I have added working columns for the last date, recent value (0 or 1) and recent status (text field). The recent columns are blank except for the most recent data rows (that part of the sheet is working). I have a project metric sheet template across topics…
Calculate Project Time TOTAL for PM across aligned projects
Hi - I am trying to figure out how to create a SUM of the Total Project Time aligned to each PM for all of their projects. On my master sheet I have the Resource Type, Resource Name, and a Helper Column for Time. The Helper Time is an estimated allotted time based on the timeframe of the project. Example: <3 months = 25…
Sheet Summary Formula
I am looking to count how many letters we have issued within each month, this is by going by the modified date, these are the formulas i have tried as i want to start this from August: =COUNTIFS(Modified:Modified, MONTH(@cell ) = 8, Modified:Modified, YEAR(@cell ) = 2025) However, it comes up with an error. Any ideas…