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!

Trending discussions

Using Control Center to Track Folder Updates?
Hello, I am unsure of the feasibility of this goal but am hoping someone could provide some insight. Within my organization we have the following structure Workspace → Workspace → Folder. Within this folder we have additional folders that are added through an API integration we have setup. I am hoping we would be able to…
Will and API token for a report give access to the Sheet the Report generated from?
Hi, I have a large Smartsheet, I want to generate a row report that reduces information displayed down to a specific group. For that Row Report, if I were to generate an API token and share it to a third party, will that third part have access to the information on the original 'Grid' sheet, or will it be reduced to what…
Were there recent changes to the totalPages parameter when querying the /sheets endpoint?
Up until last week, the totalPages and totalCount parameters functioned as expected when performing a simple GET call to /sheets. If there were 150 sheets, then it would show "totalPages": 2, "totalCount": 150. As of Friday of last week, it seems like it is showing "totalPages": 1, "totalCount": 100 any time there are more…