Formula to count every other column (dynamic range)

[Darlene]

Hello, I wrote yesterday regarding how to setup a formula to count
information which will be added on a regular basis. Pecoflyer mentioned
dynamic range. Sounds great. I checked it out but my question is how would
I do this when I only want to count every other cell because one answer will
be yes and one will be no. I want a formula to add up the no and yes in a
row on a questionnaire.

Would appreciate help/clarification/direction.

Thank you.

 

[Pecoflyer]

Could you post a small sample of your data, please?

 

[Bernard Liengme]

=COUNTIF(A1:Z1,"yes") will count the number of "yes" entries in the range
A1:Z1
It will ignore empty cells, so I am not sure why you mentioned dynamic range

If you want to count how may "yes" entries are in A1, C1, E1, ... of A1:Z1
then
=SUMPRODUCT(--(MOD(COLUMN(A1:Z1),2)=1),--(A1:Z1="yes"))

For cells B1, D1, .....
=SUMPRODUCT(--(MOD(COLUMN(A1:Z1),2)=0),--(A1:Z1="yes"))

best wishes

 

[Darlene]

Thank you Bernard and Pecoflyer. I'm not really sure if the formulas you
mention will work. On the questionnaire, Yes or No has to have an x placed
in it. So the way I see it, I would have to do COUNTA (B6, D6, F6,
H6)...these would be all yes boxes. For no answers, it would have to be
COUNTA (C6, E6, G6). So these cells do not define whether it is yes or no

Yes No

x

Yes No

x

I'm really confused now. Hope you can help me.

Thanks again.

 

[Bernard Liengme]

I suggest
for A1,C1,...
=SUMPRODUCT(--(MOD(COLUMN(A1:Z1),2)=1),--(A1:Z1="x"))
and
For cells B1, D1, .....
=SUMPRODUCT(--(MOD(COLUMN(A1:Z1),2)=0),--(A1:Z1="x"))
best wishes

 

[Darlene]

How can I send an example via copying and pasting to this message?

 

[Darlene]

I'm getting it!!! How do you put two formulas in the same cell? Can one
follow the other? I think I understand Pecoflyer mentioning dynamic range
because this questionnaire will continuously have answers added to it. So
when I tried the formula, I had to change the range to B9:J9 because I got a
circular error when I put in Z9. Does that make sense? I wish I could send
a little sample of it so you can actually see what I'm working with. I just
don't know how to do this dynamic range along with the SUMPRODUCT. Hope you
can help. Bear with me....I'm a newbie.

Thank you.

 

[Bernard Liengme]

Just send me a file (take the caps out of my email address)