How to use result from a multi-select drop down in COUNTIFS formula

Randy Van Winkle
Randy Van Winkle ✭✭✭
edited 04/16/20 in Formulas and Functions

I would like to Count the values from a row that contains 1 value from a Multi-Select and 1 of 2 values from a drop down. I have two formulas for the 2 values because 1 of the drop down values contains a < symbol. Here is what I tried to put together


The first one is not registering any values.


=COUNTIFS([Does customer supply PPE or Consumables]:[Does customer supply PPE or Consumables], "PPE", [Safety Glasses]:[Safety Glasses], "No Supply")


The second one is showing values, but does not seem to be calculating correctly


=COUNTIFS([Does customer supply PPE or Consumables]:[Does customer supply PPE or Consumables], "PPE", ([Safety Glasses]:[Safety Glasses]), CONTAINS("< 2 Week Supply", @cell))

Best Answer

Answers

  • The first value is from a Multi-Select drop down (Does customer supply PPE or Consumables) and could contain one or more of 4 selections (PPE, Chemical, Soap, Paper)

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    In the multi select column, are you wanting to count only rows that ONLY have "PPE" in them, or do you also want to count rows that have "PPE" along with some other selection in it?

  • No the second value would relate to one selection in the Multi-select. I did some more work on this and I think I solved my issue. Here are my revised formulas

    For No Supply:

    =COUNTIFS([Does customer supply PPE or Consumables]:[Does customer supply PPE or Consumables], CONTAINS("PPE", @cell), [Safety Glasses]:[Safety Glasses], "No Supply")

    For < 2 Week Supply

    =COUNTIFS([Does customer supply PPE or Consumables]:[Does customer supply PPE or Consumables], CONTAINS("PPE", @cell), ([Safety Glasses]:[Safety Glasses]), CONTAINS("< 2 Week Supply", @cell))

  • Thanks for Looking!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!