# COUNTIFS, CROSS SHEET, HAS, MULTI-SELECT DROP DOWNS AND MULTIPLE CRITERIA

Options
✭✭✭

I feel like I've been going crazy. Trying to get a count of how many [Primary Column]45 appear when the other ranges have the listed criteria. All ranges are multi-select drop downs. Keep getting 0, where I know there is at least 2.

=COUNTIFS({Rx Consumer Complaints Tracker Range 1}, HAS(@cell, \$[Primary Column]\$45), {Rx Consumer Complaints Tracker Range 1}, HAS(@cell, \$[Column2]\$45), {Rx Consumer Complaints Tracker Range 4}, HAS(@cell, \$[Primary Column]@row), {Rx Consumer Complaints Tracker Range 3}, HAS(@cell, [Column3]\$46))

Tags:

• ✭✭✭
Options

It looks like maybe your Range 1 includes more than one column. try reducing it to just the Primary Column and then make another range for Column2 (Range 2 in my formula below). Then try this:

=COUNTIFS({Rx Consumer Complaints Tracker Range 1},\$[Primary Column]\$45,{Rx Consumer Complaints Tracker Range 2},\$[Column2]\$45,{Rx Consumer Complaints Tracker Range 4},[Primary Column]@row,{Rx Consumer Complaints Tracker Range 3}, [Column3]\$46)

• ✭✭✭
Options

The first range and second ranges are for the same column.

• ✭✭✭
Options

Column2 is your primary column? This doesn't make sense. Can you send a screenshot?

• ✭✭✭
Options

I need the formula to return a count of how many rows in sheet RX Customer complaints, have the values referenced in \$[Primary Column]\$45, \$[Column2]\$45, [Primary Column]@row and [Column3]\$46.

Screenshot:

• ✭✭✭
Options

I just realized that it appears you are referencing the Primary Column twice, once at row 45 and once at the current row. If these values aren't the same, then I don't think you will ever get a count other than 0.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!