count all the columns in a range with certain conditions

**hopeace** · 10-17-2005, 04:56 PM

I need to count all the columns in a range with these conditions: if they contain entries; and are the entries only 'n' values.

'y'= goal met 'n'=goal not met

.........................................john......sues......mary.....jack
improved attendance..............y............n.....................n
increased time in class............y............y.....................n
grade/course completion.......................n........................

I want the formula to come back as =1 because only jack has these goals, and he did not achieve these goals.

In order to chart this i would also need a formula that tells me how many students had goals in their column (not how many goals, or the result, just if they had any goals).

Thanks!!!
keith

pinmaster · 10-17-2005, 08:34 PM

Hi
If it is possible, try inserting a blank row above your range, you can hide that row later. Now lets say that your range starts in column A row 5, in your blank row starting on the second column (in your example above John) put this formula:
=IF(AND(COUNTIF(B5:B7,"n")>0,COUNTIF(B5:B7,"n")=COUNTA(B5:B7)),1,0)
and copy across, to get the number of columns simply sum the row you inserted.

P.S. you will need to ajust the formula for your range.....B5:B7

HTH
JG