Assistance With Cross Sheet CountIFS Formula

Hello everyone,

I'm putting together a bar chart for a dashboard that uses several cross-sheet ranges. The formula works and I was able to put a basic table together with the "Department Affected" as the vertical axis of the table. The function is pulling from 3 ranges on a source sheet, each pulling form a different column on said sheet.

When I finished the table and created the bar chart it didn't sink in that the numbers I was looking for were far less then what I imaged. After multiple stabs at trying to diagnose the disparity, I came to the conclusion that the range labeled "Department Affected" was a multi-select dropdown column. Many of the cells in the column had 2 or more departments selected. As I was selecting and deselecting the department affected for each row the numbers were not going up or down.

Its at that point that I realized I was only returning values on cells that had a single value for department affected. So I am turning to the community to help me out on how I can return a value if the cell has 2 or more values checked.

I've pasted an example of the formula below, again this is returning a value but only of the cells with a single value.

=COUNTIFS({Range 1}, [Column2]$1, {Department affected}, "Tech Ops", {Range 2}, "RegionX")

Thank you,


Pete

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!