Cross sheet countifs, multipul drop down in cell - multipul errors.

Jason P

Hi All.

I run the current cross sheet formula which is working when there is only One request type seleced in the cell. ie; Costing. When there is Two in the cell ie, Costing Drawing the results is not counted.

I have tried changing COUNTIFS with COUNTM however this results in a count of 177, rep in question only has 13 total sheet entries! I have tried incerting CONTAINS($Category@row) but this results in #invalid operation. same result with HAS($Category@row), Any assistence greatly appreciated.

($Category$105) is the reps name

($Category@row) is the request type, I have 9

=IF(COUNTIFS({Design - Request Register Range 2}, ($Category$105), {Request Tracker Range 4 - Request Type}, ($Category@row), {Request Tracker Range 2 - Status}, "In Progress") > 0, COUNTIFS({Design - Request Register Range 2}, ($Category$105), {Request Tracker Range 4 - Request Type}, ($Category@row), {Request Tracker Range 2 - Status}, "In Progress"))

Cheers.

Philip Robbins

Hi @Jason P,

If you are referencing a multi-select field in your formula then you are on the right path with CONTAINS; you just need to work on the syntax. CONTAINS requires both a search value and a range to search in. When used with a function such as COUNTIFS, you've already specified the range in the previous part of the entry, e.g. {Request Tracker Range 4 - Request Type}. Therefore the range to enter in the CONTAINS function is @cell. So you would have something like CONTAINS(Category@row,@cell).

Note you don't need the $ before Category when referencing it unless you are planning to drag the formula left or right across columns. In fact, if you're going to end up using a column formula (which I'd recommend), you can't use a direct cell reference.

Paul Newcome

I suggest the HAS function. Your syntax is just a little off is all.

COUNTIFS({Design - Request Register Range 2}, $Category$105, {Request Tracker Range 4 - Request Type}, HAS(@cell, $Category@row), {Request Tracker Range 2 - Status}, "In Progress")

Philip Robbins

Hi @Jason P,

If you are referencing a multi-select field in your formula then you are on the right path with CONTAINS; you just need to work on the syntax. CONTAINS requires both a search value and a range to search in. When used with a function such as COUNTIFS, you've already specified the range in the previous part of the entry, e.g. {Request Tracker Range 4 - Request Type}. Therefore the range to enter in the CONTAINS function is @cell. So you would have something like CONTAINS(Category@row,@cell).

Note you don't need the $ before Category when referencing it unless you are planning to drag the formula left or right across columns. In fact, if you're going to end up using a column formula (which I'd recommend), you can't use a direct cell reference.

Paul Newcome

I suggest the HAS function. Your syntax is just a little off is all.

COUNTIFS({Design - Request Register Range 2}, $Category$105, {Request Tracker Range 4 - Request Type}, HAS(@cell, $Category@row), {Request Tracker Range 2 - Status}, "In Progress")

Jason P

Thank you both,

The metrics sheet to which this formula is on has been built over time and as requests for data come in it grows.. hence the use of $, I'm sure there's a better way thinking of pivot table but that's a little over my head and my primary role here has to come first.

Community members like yourselves with incredably fast responses make the whole Smartsheet process enjoyable, more so for those like me who struggle with the repetition to retention.

Thank you both again, as well as others who give their time to help

Regards

Paul Newcome

Happy to help. 👍️

Something to keep in mind moving forward about the differences between HAS and CONTAINS...

CONTAINS will find anything that has that string even if the string is only part of it. A very basic example would be if you are using CONTAINS to search for "1", it will grab 1, 10, 100, 21, 5678947838349756984376439671, etc..

HAS on the other hand is specifically designed for multi-select columns that have multiple entries. If the above examples were all populated in a single cell of a multi-select dropdown column and you used the HAS function, it would only grab that first one.

To use your example with categories...

Lets say you have Categories of "A" and "B", but you also have subcategories of "A1", "A2", "B1", and "B2".

If you want to pull a count for all rows that are strictly "A", you would use the HAS function so that you don't grab those sub categories. But if you wanted to just enter "A" in your metrics sheet but still pull everything that has a category or subcategory of "A", you would use the CONTAINS function.

So they both have their pros and cons. It is just a matter of knowing exactly what you want to do to determine which function is best for that particular instance.

Jason P

Nicley explaind, I was wondering if one had a distict advantage over ther other, in the end I ran with HAS.

Regards

Jason P

Hi @Paul Newcome

Just on the HAS( function.

We have two Andrew's in our design team, and using HAS I have used the full name of the other to make the Status count on my metrics sheet. Regards the other Andrew, I have HAS only looking for Andrew but it is not picking up cells where his full name is used on the Assigned to?

On my metrics sheet Andrew is noted as being assigned 1 job at Not Started - actual is 3. If I change HAS to Include his surname it counts 2 and Not Started.

Thank you.