Using Countifs with Ors and Ands

Chevon Brownell

I am looking for help with a formula. Hopefully there is one out there. I have several columns Month, 1 Assigned to, 2 Assigned to, 3 Assigned to, 4 Assigned to, and Status. I need to count if it is in say month 1 and assigned to office in any of the Assigned to columns and the status is open. Is there a way to have the ifs and the ands and the or all in one formula? So my example should come up with a count of 3.

Chevon Brownell

What I was describing is going to be the start of my data collecting. I will need to count for each department for each month for each status. I could do it in excel pretty easily but it seems to be a little harder in Smartsheets. So there are 4 Assigned To columns that there are 14 different options for. The status can be open, further action needed or closed. I need to report for each month how many open items for each department, how many closed items for each department and how many further action needed items for each department. And sometimes and item can be assigned to multiple departments, hence the need to look for the department in any of the 4 Assigned To columns. I hope that explains it a bit better.

Mike Wilday

What does this formula give you? You basically don't need to add ANDS when the content is in separate columns, simply repeat the search in each column.

=countifs(Month:Month, 1, [1. Assigned to]:[1. Assigned to], "O - Office", [2. Assigned to]:[2. Assigned to], "O - Office", [3. Assigned to]:[3. Assigned to], "O - Office", [4. Assigned to]:[4. Assigned to], "O - Office")

Kelly Moore

Hey Chevon

Although a clunkier solution than Mike's, I add a helper checkbox column to my sheets when I am evaluating an OR condition across columns in a row for criteria within SUMIFS, COUNTIFS, etc. In your case it is evaluating your four [Assigned to columns] by row.

Using this approach, the formula in your checkbox helper column is

=IF(COUNTIFS([1. Assigned To]@row:[4. Assigned To]@row, "O. Office") > 1, 1)

A check will indicate 'Office' was found in at least one of the four columns.

The COUNTIFs formula you're asking about becomes

=COUNTIFS([helper checkbox]:[helper checkbox], 1, Month:Month, 1, Status:Status, "Open")

Chevon Brownell

What I was describing is going to be the start of my data collecting. I will need to count for each department for each month for each status. I could do it in excel pretty easily but it seems to be a little harder in Smartsheets. So there are 4 Assigned To columns that there are 14 different options for. The status can be open, further action needed or closed. I need to report for each month how many open items for each department, how many closed items for each department and how many further action needed items for each department. And sometimes and item can be assigned to multiple departments, hence the need to look for the department in any of the 4 Assigned To columns. I hope that explains it a bit better.