difficult calculation

J

joolz46

HI,
I have a column with yes/no or blank answers to be reflected in another
column with a 1 for y or n and a blank for a blank answer. I then need a
total for all the 1's in the same column! Sorry if this sounds a bit
gobbledegook. its the best I can do!
 
J

JE McGimpsey

Not sure why you need another column, but you could use

=IF(A1="y",1,0)

or the equivalent

=--(A1="y")

(where -- is two minuses together)

To count the number of y's:

=COUNTIF(A:A,"y")

If you're trying to count both y's and n's in the same column, use

=IF(OR(A1="y",A1="n"),1,0)

or

=IF(A1<>"",1,0)

or their equivalent

=(A1="y")+(A1="n") and =--(A1<>"")


To count y's and n's in the same column.

=COUNTIF(A:A,"y")+COUNTIF(A:A,"n")
 
R

Roger Govier

Assuming the question to be in column A and the response in column B then in
C1
=IF(B1="",0,1)
Copy the formula down the cange of column C to match your range of data.
Sum the range of column C,
=SUM(C1:C100) for example
 
Top