Countif Array

[Mike]

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.

 

[Pete_UK]

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

 

[Mike]

Pete

My YES and NO answers are not in seperate columns, they are in the same
column. I used a list to select YES or NO in each cell.

Can you respond please?

 

[Pete_UK]

I wasn't sure if you wanted just one column of results, i.e. any answer for
each department, or if you wanted it like the table I gave you. At least
with the 3 column table it is easy to add the Yes and No columns together.

Actually, the formula needs a bit of amendment:

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

Put this in E2, and when you copy it to F2 it will still be looking at
columns A and B, then copy these two cells down.

If you only want a single column against each department (i.e. no
distinction between yes and no), then put this formula in E2 and copy down:

=COUNTIF(A:A,D2)

Hope this helps.

Pete