COUNTIF or SUMPRODUCT with multiple criteria
 

I have a survey where Yes and No answers are signified by "1" or "0" in a
series of rows (each row corresponding to a different question in the survey)
and where race of respondent is indicated by "B" (for Black), "W" (White) or
"L" (for Latino" in one of the rows. I want to create a formula that counts
the number of occurrences of "1" in a range of cells in one row where "B" (or
W or L) occurs in a cell range in another row. In other words, where both
criteria are met--e.g. how many Black people answered Yes to a particular
question, how many white people answered No to a particular question, etc.

My inclination is to use COUNTIF but I am not clear if such a formula is
possible with multiple criteria. I have never used SUMPRODUCT, but I have
seen that recommended on these forums.

Any ideas?

COUNTIF or SUMPRODUCT with multiple criteria
 

As you have 1's and 0's in one column, you can use SUMIF as the answer
will be equivalent to counting. So, suppose your ethnicity code is in
column E and your answers are in column H - this will give you the
number of yes answers for Latinos:

=SUMIF(E:E,"L",H:H)

This will give you the number of no answers for Blacks:

=COUNTIF(E:E,"B") - SUMIF(E:E,"B",H:H)

(i.e. the total number of answers from Blacks minus the number of yes
answers for Blacks).

Perhaps you can see how to adapt this to your other requirements.
SUMIF and COUNTIF are much faster than SUMPRODUCT.

Hope this helps.

Pete

On Nov 25, 6:17*pm, ERICinLA77
wrote:
I have a survey where Yes and No answers are signified by "1" or "0" *in a
series of rows (each row corresponding to a different question in the survey)
and where race of respondent is indicated by "B" (for Black), "W" (White) or
"L" (for Latino" in one of the rows. I want to create a formula that counts
the number of occurrences of "1" in a range of cells in one row where "B" (or
W or L) occurs in a cell range in another row. In other words, where both
criteria are met--e.g. how many Black people answered Yes to a particular
question, how many white people answered No to a particular question, etc..

My inclination is to use COUNTIF but I am not clear if such a formula is
possible with multiple criteria. I have never used SUMPRODUCT, but I have
seen that *recommended on these forums.

Any ideas? *