count if formula help

patrick_daniels

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

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.