Checkbox with multiple unique identifier rows

I have a checkbox column that I want to set up a formula for. This formula will reference another sheet for a checkbox, using a unique identifier. If the box is checked in any of the unique identifier rows in the reference sheet, it will return a checked box in the formula sheet.

The formula I found is:

=IF(COUNTIFS(IDColumn,[Unique ID]@row ,CheckedColumn,1)> 0,1,0)

This translates into the following for my columns.

=IF(COUNTIFS({Case Number1}, [Case Number]@row , {Expedite1}, 1) > 0, 1, 0)

I'm not getting an error message, but the box is not being checked.

The unique identifier is "08459402" on the SOW Request Sheet. One of the boxes for Expedite1 is checked for that unique identifier, so it should be returning a checked box on the 3-Week Expedite Box.

The 3-Week Expedite box contains the formula and should have the box checked.

Can someone help with why the box on 3-Week Expedite is not checked, please?

Find more posts tagged with

Formulas

Accepted answers

Darren Mullen

@Kasey Mabary Since you have leading zeros on those numbers, my guess is that one of the sheets has a ' infront of the value, and one doesn't.

You'll also need to mind that numbers can be interpreted as text in some sheets and not in others. Best to force them all to numbers or text using either the value() function in your formulas or concatenating "" to the front of the values to make them all text.

All comments

Darren Mullen

@Kasey Mabary Since you have leading zeros on those numbers, my guess is that one of the sheets has a ' infront of the value, and one doesn't.

jmyzk_cloudsmart_jp

Hi @Kasey Mabary

A formula that comes naturally to me is the one that uses the INDEX and MATCH functions to access the Boolean data directly.

[3-Week Expedite] =IFERROR(INDEX({Expedite1}, MATCH([Case Number]@row, {Case Number1}, 0)), false)

https://app.smartsheet.com/b/publish?EQBCT=46a3c52d464143b785c3ca1c3ae7b735

As for the reason why your seemingly correct formula fails, as @Darren Mullen has pointed out, the case numbers with zero at the top can be interpreted as text.

As I tested, when I put a number on the SWO request sheet, as the sixth row in the image below, your formula worked OK. This suggests that Smartsheet treats the {Case Number1} range as is, either as text or a number. However, at the COUNTIF's condition, if you simply put ",[Case Number]@row", it treats it as a number.

Based on the idea, the first and third formulas convert both case numbers to text.

The first uses = in front, as =[Case Number]@row, forcing a comparison to a text value, and the third adds "".

The second formula forces both case numbers to a value.

[3-Week Expedite : Revised Count] =
 =IF(COUNTIFS({Case Number1}, =[Case Number]@row, {Expedite1}, 1) > 0, 1, 0)


[Darren Mullen : Value] =
  IF(COUNTIFS({Case Number1}, VALUE(@cell) = VALUE([Case Number]@row), {Expedite1}, 1) > 0, 1, 0)


[Darren Mullen : +""] =
  IF(COUNTIFS({Case Number1}, @cell + "" = [Case Number]@row + "", {Expedite1}, 1) > 0, 1, 0)

https://app.smartsheet.com/b/publish?EQBCT=23a84c0b532944afa52f5a70fd5585b5

Kasey Mabary

@Darren Mullen That worked! It was the piece that I was missing, thank you so much!! :)

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.