Assistance With Cross Sheet CountIFS Formula

Pete Photis

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

Heather Duff

@Pete Photis You may want to check out the HAS function. https://help.smartsheet.com/function/has

It detects whether a multi-select field contains that specific text - regardless of whether other items are selected in addition to it.