Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? * |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Multiple criteria countif/sumproduct function | Excel Worksheet Functions | |||
COUNTIF or SUMPRODUCT counting multiple criteria | Excel Worksheet Functions | |||
COUNTIF or SUMPRODUCT counting multiple criteria | Excel Worksheet Functions | |||
countif/sumproduct on multiple criteria | Excel Worksheet Functions | |||
Using COUNTIF with 2 criteria - SUMPRODUCT? | Excel Worksheet Functions |