Countifs with OR across multiple columns

Hilton58

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?

 

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