IF(count) function

Adam Qualls

I have a smartsheet that is performing a lookup on another smartsheet to see if a value exists. It doesn't seem to be working though because it always comes back as false. Any ideas what I might have done wrong?

Smartsheet 1:

=IF(CONTAINS([Cost Center]@row, {RML Pulse Cost Accounting Range 1}), "Green", "Red")

[Cost Center]@row = 7201770551

Range {RML Pulse Cost Accounting Range 1} = Entire column 1 on other smartsheet.

Column 1 in other smartsheet contains 232 rows and 1 row equals 7201770551

It always returns red though and never green. I tried setting the range equal to just a single cell that contained the 7201770551 value but that didn't work either.

Any suggestions?

Adam Qualls

I found a way around this by converting my lookup column 1 on the other smartsheet to have =7201770551 + "" which I guess converts it to text. I don't understand why they would not allow the function to find numeric data. This fixes my issue, but if there is a better way to achieve this outcome, I would be interested to know for future use.

Kelly Moore

Hey Adam

Your original formula will work with a slight tweak

=IF(CONTAINS([Cost Center]@row, {RML Pulse Cost Accounting Range 1})="true", "Green", "Red")

As a note, the CONTAINS function doesn't look for an exact match. In your example of

[Cost Center]@row = 7201770551

a true would also be returned in values that had extra digits at the beginning and/or at the end of the the value, ex 1117201770551999. If this is a concern, the HAS function looks for exact matches.

cheers

Adam Qualls

https://community.smartsheet.com/discussion/comment/274153#Comment_274153

@KDM

I tried your suggestion but same result, it always goes to Red. I think the problem is that the contains function cannot evaluate a numeric field. I found another user stating this.

Kelly Moore

Sorry, I think you're right. Yesterday I must have had a space in the field - I can't replicate it today but I easily toggled between red and green yesterday. Today I had to add a letter to the number for it to pick it up.

This formula does work on my sheet. If the COLLECT finds anything, then the count being greater than zero is true.

=IF(COUNT(COLLECT({RML Pulse Cost Accounting Range 1}, {RML Pulse Cost Accounting Range 1},[Cost Center]@row = @cell)) > 0, "Green", "Red")