Countifs with OR across multiple columns

Hello all, I'm a fairly new (2 weeks) smartsheet user and I'm trying to write a formula that will count several columns based on specfic criteria.

I want to count the number of Open projects (status) that are associated with engineering by either person/dept assigned column 1 or 2, and are of a safety concern (category)

The problem I think I'm having there are several people in the columns that are in engineering and they all need accounted for.

So it would be the total number of projects assigned to engineering (through person or department) and are in the safety category. I attached a picture to try to explain better. Can anyone provide any insight?

Find more posts tagged with

Manufacturing

API and Developers

Product Development

Project Management

Cell linking

Comments

Paul Newcome

Is it possible to have a name or department that is NOT associated with engineering?

L_123

I would make a department sheet and use an index match to find the persons department. basically a little side database. You can use it for all sorts of purposes other than this, but with one column being name and another being department, you can reference that to make future edits much easier, and simplify this formula.

That said, the formula I have here would let you keep just a single sheet, but it is a little complicated and a little long. It's tested and works though. Making a database is a much simpler approach and much more efficient for future edits.

=COUNT(COLLECT([Person/Dept Assigned]:[Person/Dept Assigned], [Person/Dept Assigned]:[Person/Dept Assigned], OR(@cell = "Engineering", @cell = "Name 1", @cell = "Name 2"), Status:Status, "Open", Category:Category, "Safety")) + COUNT(COLLECT([Secondary Person/Dept Assigned]:[Secondary Person/Dept Assigned], [Secondary Person/Dept Assigned]:[Secondary Person/Dept Assigned], OR(@cell = "Engineering", @cell = "Name 1", @cell = "Name 2"), Status:Status, "Open", Category:Category, "Safety")) - COUNTIFS([Person/Dept Assigned]:[Person/Dept Assigned], OR(@cell = "Engineering", @cell = "Name 1", @cell = "Name 2"), [Secondary Person/Dept Assigned]:[Secondary Person/Dept Assigned], OR(@cell = "Engineering", @cell = "Name 1", @cell = "Name 2"), Status:Status, "Open", Category:Category, "Safety")

L_123

This stuff makes me with we had a NOR and NAND function for smartsheet.

Paul Newcome

I am hoping that the only names would be engineering related which would make for a pretty simple COUNTIFS statement. Otherwise I was going to suggest making a table and using a (relatively simple) INDEX/MATCH function.

L_123

Yeah the issue is that there are two columns being compared and that there is a list of names. I think even if there is a database it is going to be a 2 countif statements because you have to subtract where both criteria are met in the personnel columns. But it would be much easier I agree.

There is something there if he uses an index match to compare the two, but that makes it even more complicated, and i'm not convinced it would make it shorter or faster.

It would probably be a good idea to do a helper column to make this a little more straightforward

A checkbox column with

=if(or(index({department:department},match(contact1@row,{Name:Name},0)) = "Engineering",index({department:department},match("contact2@row",{Name:Name},0)) = "Engineering")),1,0)

Or something like that. Then just use a countifs using the checkbox field

Paul Newcome

Luke is on to something there...

L_123

So I reread through all of this and it is pretty jumbled, especially for a new user. So I've made an example of what I recommended that you can simply go in and look at the formulas.

Main sheet where you will be doing the calculations:

https://app.smartsheet.com/b/publish?EQBCT=6804bcdd27684685a4dd4545451c74ba

Employee Database

https://app.smartsheet.com/b/publish?EQBCT=7ef11ab9315c45279f83269e73b775f3

Sign in to join the conversation!

Quick Links

Trending discussions

Keeping the API token secure on a shared hosting website. Using PHP
I have a website on shared hosting. I know that I should keep api tokens out of public_html folders on said website. I would like to use a <domain>/config/config.php file to hold those api tokens for Smartsheets. This approach is working for another application that I integrate with from this website. However, when I put…
what is the cause of "Unexpected character encountered while parsing value: <. Path '', line 0, pos"
what is the cause of "Unexpected character encountered while parsing value: <. Path '', line 0, position 0." error when using the C# SDK. the full log message is: - 2025-09-18 09:58:54 *** Exception in 'ProcessShares(Entity, EntityName, EntityId, Counter, ssWorkspace)' 2025-09-18 09:58:54 1 'ShareResources.ListShares()'…
Update Columns API Possible Bug - unable to clear all options in a MULTI_PICKLIST column
NOTE: I tried to file this several times a support ticket, but the AI BOT was unable to create a ticket and the OG Support Portal no longer allows the direct creation of tickets. Grrrrrr. ISSUE: It appears that the Update Column API PUT https://api.smartsheet.com/2.0/sheets/{sheetId}/columns/{columnId} fails to work as…