Assistance Needed- Count of Cell with Multiple Functions Selected

I am trying to add cells that meet specific criteria (2 selections). In the attached example, the answer should be 4 cells combining "Order Coordinator" and "Import/Export" entries. Can someone support me with the appropriate formula?

I appreciate any help you can provide.



Bst. Rgds,

Anthoni

Answers

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭

    Hi @Anthoni,

    If you're after a count of cells within Functional area that have both "Order coordinator" and "Import/Export" and excluding any other 2 selection options then this formula should do what you're after:

    =COUNTIF([Functional Area]:[Functional Area], AND(HAS(@cell, "Order coordinator"), HAS(@cell, "Import/Export")))

    Hope this helps, but if you've any problems/questions then let us know.

  • Samuel Mueller
    Samuel Mueller Overachievers

    @Anthoni try this formula


    =countifs([functional area]:[functional area], and(has(@cell,"Order Coordinator"),has(@cell,"Import/Export"))

  • To add clarity to perform the calculation, some cells contain these two functional areas (Order Coordinator and Import/Export) and some other functional areas. I only want to count the ones that contain only (Order Coordinator and Import/Export).



  • Samuel Mueller
    Samuel Mueller Overachievers
    edited 02/29/24

    hmm. You could add a LEN criteria...

    =countifs([functional area]:[functional area], and(has(@cell,"Order Coordinator"),has(@cell,"Import/Export"), LEN(@cell)=25)

    I made 25 up you will need to find the length of both those options together. In a temporary cell do =LEN([Functional Area]@row) to get the number

  • Samuel Mueller
    Samuel Mueller Overachievers

    @Anthoni more simply you could just do

    =COUNTIFS([functional area]:[functional area], "Order Coordinator" + CHAR(10) + "Import/Export")

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!