Count for multiple values in a range

Hello,

I'm having some difficulty setting up a countif formula for my sheet. I have a long list of barcodes as a range, and I'm trying to count how many times specific barcodes show up in the list. I currently have it set up to count for two barcodes each in separate columns.

=IFERROR(IF(ISBLANK(Barcode@row ), "", SUM(COUNTIFS({Past Barcodes}, Barcode@row , {Past Barcodes}, NOT(ISBLANK(@cell ))), COUNTIFS({Past Barcodes}, [Old Barcodes]@row , {Past Barcodes}, NOT(ISBLANK(@cell ))))), "Can't Find")

However, I'm running into the issue where I now need to count for an open ended number of barcodes. I was hoping to updated the "Old Barcodes" column to a multiple value dropdown list, and then check if any of the values appear in the "Past Barcodes" range.

I'm struggling to find any posts that are relevant, and can only find examples for either checking through a dropdown list for a single value (I need the opposite) or for checking for a limited number of values (Which I'm already doing and would like to be able to check for a variable number)

Please let me know if anything is not clear and I'd be more than happy to explain further.

Thank you!

Spencer

Find more posts tagged with

Formulas

Accepted answers

All comments

Antonie B

Hi @Spencer_W ,.

If I am understanding this correctly, you need to check the columns "Past Barcodes" & "Old Barcodes" for matches for the barcode reference "Barcode@row" - where both "Past Barcodes" & "Old Barcodes" are created as a multi-select dropdown?

If so, you want to use the function HAS, which has been created specifically for Multi Select dropdown and contact columns. https://help.smartsheet.com/function/has

The formula would look like this:

=IFERROR(IF(ISBLANK(Barcode@row ), "", SUM(COUNTIFS({Past Barcodes}, HAS(@cell, Barcode@row) , {Past Barcodes}, NOT(ISBLANK(@cell ))), COUNTIFS({Past Barcodes}, HAS(@cell , Barcode@row ) , {Past Barcodes}, NOT(ISBLANK(@cell ))))), "Can't Find")

I hope this helps

Spencer_W

Hello @Antonie B ,

Unfortunately, I'm trying to set up kind of the opposite.

The "Barcode" and "Old Barcodes" columns are both multi select dropdowns, but they are checking against the "Past Barcodes" range which is a standard text/number column.

My overall workflow is that the "Past Barcodes" range is populated by a form when a barcode is used and scanned. This formula that I'm trying to make is then on another sheet I am using to track information about how often each specific barcode is scanned.

The "Barcode" column refers to the specific barcode that is currently physically on an item.
The "Old Barcodes" multi select dropdown column refers to barcodes that have previously been on that specific item, but have since been replaced.

My goal is to track all of the times any of these barcodes (which all refer to the same physical item) have been scanned, without having to go in and add an extra column (old barcode 1, old barcode 2, etc etc) each time a barcode is replaced.

Hopefully that extra explanation helps explain my goal. Please let me know if you have any ideas! And thank you for your help!

Antonie B

@Spencer_W,

Thanks for the follow up!

I wonder, if you may want to switch the tactic to do the following:

Have one formula that just counts the number by the barcode used at the time (in "Past Barcodes") and have that number than be reflected on the sheet where the form is submitted to.

Then on your tracking sheet, you use the Specific Item as the Unqiue reference, pulling together a little mini table of each barcode and how many times is used! A second column is then used, using JOIN to combine the barcode and the entry together as a label of sorts.

The formula would use the functions JOIN/COLLECT/DISTINCT and CHAR

The formula would look something like this:

=JOIN(DISTINCT(COLLECT({Barcode Label Range}, {Specific Item Range}, "Specific Item")), CHAR(10))

If you then apply text wrapping to that column, it will be neatly organised in a list.

I hope this helps?

Spencer_W

Hello @Antonie B!

I didn't particularly want to have to rework the workflow all that much, so I didn't want to totally go with that setup you're suggesting.

However, when reading through your idea, it made me think of a slightly different process as well, which led me to finding a different community post that was very helpful!

I could've sworn that I had tried this previously, but I tried the following formula, and it works perfectly! Very similar to your original suggestion, just swapping the order of the arguments in the HAS function.

=IFERROR(IF(ISBLANK(Barcode@row ), "", SUM(COUNTIFS({Past Barcodes}, Barcode@row , {Past Barcodes}, NOT(ISBLANK(@cell ))), COUNTIFS({Past Barcodes}, HAS([Old Barcodes]@row , @cell ), {Past Barcodes}, NOT(ISBLANK(@cell ))))), "Can't Find")

Thank you for your help!

Spencer Whisenand

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.