Assistance Needed- Count of Cell with Multiple Functions Selected

Anthoni

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

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

@Anthoni try this formula

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

Anthoni

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

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

@Anthoni more simply you could just do

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