Countif - Array Question
 

I want to use a function to do determine the number of Yes and No Answers to
different questions from different departments. For example, here is a table:

DEPT QUESTION A QUESTION B QUESTION C
Adm No No Yes
Adm Yes Yes No
Comm No No
Fin Yes Yes Yes
Fin Yes No No

I want to add up the number of responses like so

QUESTION A QUESTION B QUESTION C
Adm Yes 1 1 1
No 1 1 1
Comm Yes ................
No

I tried using a COUNTIF array function like this:

COUNTIF (if ((DEPT Array="ADMIN"), QUESTION A Array, ""), "Yes")

This is not working - PLEASE HELP!

Countif - Array Question
 

=sumproduct((rnga="Admin")*(rngb="yes"))

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Mike" wrote in message
...
I want to use a function to do determine the number of Yes and No Answers
to
different questions from different departments. For example, here is a
table:

DEPT QUESTION A QUESTION B QUESTION C
Adm No No Yes
Adm Yes Yes No
Comm No No
Fin Yes Yes Yes
Fin Yes No No

I want to add up the number of responses like so

QUESTION A QUESTION B QUESTION C
Adm Yes 1 1 1
No 1 1 1
Comm Yes ................
No

I tried using a COUNTIF array function like this:

COUNTIF (if ((DEPT Array="ADMIN"), QUESTION A Array, ""), "Yes")

This is not working - PLEASE HELP!

Countif - Array Question
 

Try SUMPRODUCT if you have more than 1 condition to check.

=SUMPRODUCT(--(A2:A100="Adm"),--(B2:B100="Yes"))

HTH,
Elkar


"Mike" wrote:

I want to use a function to do determine the number of Yes and No Answers to
different questions from different departments. For example, here is a table:

DEPT QUESTION A QUESTION B QUESTION C
Adm No No Yes
Adm Yes Yes No
Comm No No
Fin Yes Yes Yes
Fin Yes No No

I want to add up the number of responses like so

QUESTION A QUESTION B QUESTION C
Adm Yes 1 1 1
No 1 1 1
Comm Yes ................
No

I tried using a COUNTIF array function like this:

COUNTIF (if ((DEPT Array="ADMIN"), QUESTION A Array, ""), "Yes")

This is not working - PLEASE HELP!

Countif - Array Question
 

Thank you so much. This works great...now I have to figure out why!

"Don Guillett" wrote:

=sumproduct((rnga="Admin")*(rngb="yes"))

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Mike" wrote in message
...
I want to use a function to do determine the number of Yes and No Answers
to
different questions from different departments. For example, here is a
table:

DEPT QUESTION A QUESTION B QUESTION C
Adm No No Yes
Adm Yes Yes No
Comm No No
Fin Yes Yes Yes
Fin Yes No No

I want to add up the number of responses like so

QUESTION A QUESTION B QUESTION C
Adm Yes 1 1 1
No 1 1 1
Comm Yes ................
No

I tried using a COUNTIF array function like this:

COUNTIF (if ((DEPT Array="ADMIN"), QUESTION A Array, ""), "Yes")

This is not working - PLEASE HELP!