Assume your departments are listed in column A and the yes/no answers
are in column B. List each of your departments in column D starting in
D2 and put "Yes" in E1 and "No" in F1, so that you will have something
like this:

D E F
Yes No
Admin
Personnel
Sales
etc

Then in E2 put this formula:

=SUMPRODUCT((A1:A1000=$D2)*(B1:B1000=E$1))

You can copy the formula into F2, then copy E2:F2 down until you have
covered your list of departments. You will have the count of Yes and
No under those columns for each department - is this what you want?

Hope this helps.

Pete

On Dec 5, 8:27 pm, Mike wrote:
I am having trouble using the countif function with an array. I have two
columns, one of which is the department column (with department names in each
cell) and the other column is question column (with YES and NO answers in
each cell). I want to count the number of YES and NOs for each department.

I tried to use this countif array function: {=countin(if((A2:A21="ADMIN"),
B2:B21, ""), "YES")}

Can you help me please? I keep getting the VALUE error.