Counting with multiple conditions

QuestionEdit

QUESTION: I have a spreadsheet with slightly more than 1,000 survey responses, each response is a separate row. There are 10 questions, each which have an optional comments field. I am able to obtain the total number of comments across all responses with =COUNTA(L2:U2000). How do I count the number of responses (rows) that contain one or more comments? In other words, if there are 100 comments contained in 40 of the 1,000 responses, the formulae would show a result of 40. The second question, would be how to modify the formula above using a demographic value with cells A2:A2000 containing US states, so if the formula was IF(A2:A2000=¡°Virginia¡±), then ¡­ the answer would be the number of responses from Virginia that contain comments.

ANSWER: Chris,

Assume the next available column is V and the data starts in row 2, so in V2 put in the formula

=countA(L2:U2)

then drag fill this formula down to row 2000

now you can use countif

=Countif(V2:V2000,">0")

for virginia

=sumproduct(--(A2:A2000="Virginia"),--(V2:V2000>0))

if you have2007 or later you can use the new countifs (with an S on the end) formula

=countifs(A:A,"Virginia",V:V,">0")

Again, this is only available in Excel 2007 and 2010

---------- FOLLOW-UP ----------

QUESTION: How could a formula using what you provided =countA(L2:U2) and =sumproduct(--(A2:A2000="Virginia"),--(V2:V2000>0)) be combined and contained in a separateWorkbook used for calculating statistical data about a separate spreadsheet which contains the survey results. Essentially, I export and open the survey responses for today from the web server, and then open the Analysis.xls file. It in turn recalculates everything related to the export.xls file. Obviously all of the cell formulae reference to export.xls and preceded by [Export.xls]Sheet1!V2:V9000..., etc.

In the analysis.xls file I created a column (lets say V) for each state and wrote a formula for rows 2 through 2000 that look like this: =IF([Export.xls]Sheet1!A2="Virginia",COUNTA([Export.xls]Sheet1!$L2:U2)), and in cell A1 the formula is =SUMPRODUCT(--(V2:V2000>0)). The answer was as expected the number Virginia respondents that entered one or more comments, but can this be done using a single formule?

Many

AnswerEdit

Chris,

You could adjust this formula to do that if I correctly understand what you want:

=SUMPRODUCT(($A$2:$A$2000="Virginia")*((($L$2:$L$2000<>"")+($M$2:$M$2000<>"")+($N$2:$N$2000<>"")+($O$2:$O$2000<>"")+($P$2:$P$2000<>"")+($Q$2:$Q$2000<>"")+($R$2:$R$2000<>"")+($S$2:$S$2000<>"")+($T$2:$T$2000<>"")+($U$2:$U$2000<>""))>0))