Hi all!

Looking for some expertise with COUNTIFS, HAS, and CONTAINS formulas when also using reference sheets with dropdowns.

I have one reference sheet that my team is using to track employee's learning progress with class enrollments and completions. One of the columns is set up to be an Enrollment Status dropdown with the options: Invited, Enrolled, Completed, Declined. Another column on the sheet has which Coast they are sitting on (East or West) — *set up as a VLOOKUP, not as a dropdown.

I need a formula that counts the different combinations of the two. For example, if they sit on the West Coast (WC) and have Completed the class; or if they sit on the East Coast (EC) and have Completed the class; and so on for each dropdown enrollment option.

I started with: =COUNTIF({Fall Nominations Range 2}, "WC") which works to count the total employees on the WC— but I cannot figure out how to successfully incorporate the Enrollment Status dropdown. I tried adjusting the above formula to a COUNTIFS formula and using either HAS and CONTAINS with "Completed" but keep receiving different error messages.

My latest attempts were as follows and resulted in #incorrect argument:

=COUNTIFS({Fall Nominations Range 2}, "WC", CONTAINS("Completed", {Fall Nominations Range 7}))

I also tried:

=COUNTIFS({Fall Nominations Range 2}, "WC", HAS({Fall Nominations Range 7}, "Completed"))

Can anyone help me understand what I am missing? Thank you in advance :)