I have ranges and the second range has more than one value in each cell. With a single value - the

Naeem Ejaz

Application - SmartSheet - need someone who knows Smartsheet formulas/functions.

I need help with a formula/function - using countifs - I  have ranges and the second range has more than one value in each cell. With a single value - the formula works - with more than one, it doesn't

Range1 = Office Column

Range2 = Status Column (Fixed, OK, Unknown)

Office            Fixed    OK    Unknown

Sales                 2          7           12

Marketing         5         11           8

HR                     12           5           9

The formula using COUNTIFS - using 'Sales' use Range1 to get Status from Range2 = 'Fixed' and returns a count and the count = 2. However, if the Status Column for Sales has one cell with both 'Fixed' and 'OK' -it doesn't work.  The Status is s dropdown with more than one value

Sample of the formula.  I was hoping to find a way to use this formula to look for more than one Status since Status in Range2 needs to be dropdown and record more than one value.

Need this ASAP

The example above is in one Smartsheet but, of course, the two ranges are in a different Smartsheet.

Paul Newcome

Try this:

=COUNTIFS({Office Column}, @cell = "Sales", {Status Column}, HAS(@cell, "Fixed"))