Formula to count every other column (dynamic range)

  • Thread starter Thread starter Darlene
  • Start date Start date
D

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.
 
=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
 
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.
 
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
 
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.
 
Back
Top