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.

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


--
Pecoflyer

Cheers
------------------------------------------------------------------------
Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=31129

=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

--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Darlene" wrote in message
...
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.

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" wrote:

=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

--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Darlene" wrote in message
...
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.

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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Darlene" wrote in message
...
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" wrote:

=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

--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Darlene" wrote in message
...
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.

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

"Darlene" wrote:

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" wrote:

=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

--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Darlene" wrote in message
...
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.

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.

"Darlene" wrote:

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.

Just send me a file (take the caps out of my email address)
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Darlene" wrote in message
...
How can I send an example via copying and pasting to this message?

"Darlene" wrote:

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" wrote:

=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

--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Darlene" wrote in message
...
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.