Trying to get the number of instances of a value from a column with a dropdown list property

cabbsman

I have a field (column) in my source sheet named [Hub/Site CLLI]@row that I need to find the number of instances contained in a drop down list named {Tinas node_comparison - All CLLIs} in the target sheet. When found I need to count the number of instances of the "NTODAANODE Ticket #" found in the target sheet. This is noted as {Tinas node_comparison - NTODAANODE Ticket #} in the formula.

When I run the function below, I keep getting a count of "0" even though I know that the [Hub/Site CLLI]@row is located in a dropdown list in the target sheet's {Tinas node_comparison - All CLLIs} column.

=SUMIF({Tinas node_comparison - NTODAANODE Ticket #}, CONTAINS([Hub/Site CLLI]@row, {Tinas node_comparison - All CLLIs}))

With the information I provided, can you suggest what I may be doing incorrectly?

cabbsman

I found this article Formula to count the number of instances in a column from a multi-entry cell — Smartsheet Community and it worked! So, I'm closing this question. Thank you for taking a look. Here's the formula I wound up using.

=COUNTIFS({Tinas node_comparison - All CLLIs}, HAS(UPPER([Hub/Site CLLI]@row), UPPER(@cell)))

JamesB

@cabbsman

Try using the HAS formulas instead of the CONTAINS formula.

cabbsman

No, the "HAS" didn't work. Still getting 0 counts.

JamesB

@cabbsman I just reread your formula. We need to adjust the way it is built.

SUMIF, is RANGE to referenece, what the CRITERION is for that range, and then what RANGE to sum from.

Try this...

=SUMIF({Tinas node_comparison - All CLLIs},CONTAINS([Hub/Site CLLI]@row, @cell),{Tinas node_comparison - NTODAANODE Ticket #})

cabbsman

I found this article Formula to count the number of instances in a column from a multi-entry cell — Smartsheet Community and it worked! So, I'm closing this question. Thank you for taking a look. Here's the formula I wound up using.

=COUNTIFS({Tinas node_comparison - All CLLIs}, HAS(UPPER([Hub/Site CLLI]@row), UPPER(@cell)))

cabbsman

Hi, I'll take a look, thanks.