Using Countifs with Ors and Ands
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.
Best Answer
-
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.
Answers
-
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")
-
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")
-
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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 410 Global Discussions
- 219 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!