I just had a rush job to do up an Excel sheet while my supervisor dictated
the data to me as she interpreted the emails/faxes with various responses.
One column in particular had YES and NO as the possible data type. In the
end, I was to total up the number of YES answers and put the total in one
cell; and then to do the same for the NO answers. These YES's and NO's are
all in one column, unfortunately, but the sum of each we put into 2 cells.
Is there a formula that I could have used for each just substituting a value
that would work with the YES answers and another that would return the total
of the NO answers?

I had a filter and because this was a rush just filtered for each and
counted them up and inserted figure manually, but it would be good to
automate this as they'll need to be inputting more data as responses come in
over the next 5 days. Hoping there's a way to deal with the two types of
data.

Thank you!! :oD

=countif(a:a,"yes")

would count the number of Yes's in column A.

="Total Yes's: " & countif(a:a,"yes") & char(10)
& "Total No's: " & countif(a:a,"no")

all one cell with wrap text turned on would look like this:

Total Yes's: 12
Total No's: 13

But I'd use separate cells to make any further calculation easier.


StargateFanFromWork wrote:

I just had a rush job to do up an Excel sheet while my supervisor dictated
the data to me as she interpreted the emails/faxes with various responses.
One column in particular had YES and NO as the possible data type. In the
end, I was to total up the number of YES answers and put the total in one
cell; and then to do the same for the NO answers. These YES's and NO's are
all in one column, unfortunately, but the sum of each we put into 2 cells.
Is there a formula that I could have used for each just substituting a value
that would work with the YES answers and another that would return the total
of the NO answers?

I had a filter and because this was a rush just filtered for each and
counted them up and inserted figure manually, but it would be good to
automate this as they'll need to be inputting more data as responses come in
over the next 5 days. Hoping there's a way to deal with the two types of
data.

Thank you!! :oD

--

Dave Peterson

Try this:
B1: =COUNTIF(A:A,"YES")
B2: =COUNTIF(A:A,"NO")

Does that help?

***********
Regards,
Ron


"StargateFanFromWork" wrote:

I just had a rush job to do up an Excel sheet while my supervisor dictated
the data to me as she interpreted the emails/faxes with various responses.
One column in particular had YES and NO as the possible data type. In the
end, I was to total up the number of YES answers and put the total in one
cell; and then to do the same for the NO answers. These YES's and NO's are
all in one column, unfortunately, but the sum of each we put into 2 cells.
Is there a formula that I could have used for each just substituting a value
that would work with the YES answers and another that would return the total
of the NO answers?

I had a filter and because this was a rush just filtered for each and
counted them up and inserted figure manually, but it would be good to
automate this as they'll need to be inputting more data as responses come in
over the next 5 days. Hoping there's a way to deal with the two types of
data.

Thank you!! :oD

Use the COUNTIF() function:

=COUNTIF(A1:A100,"YES") to count the YES replies

=COUNTIF(A1:A100,"NO") to count the NO replies

change the A1:A100 to whatever range you use

--
Cutte
-----------------------------------------------------------------------
Cutter's Profile: http://www.excelforum.com/member.php...nfo&userid=984
View this thread: http://www.excelforum.com/showthread.php?threadid=49169

On Wed, 7 Dec 2005 17:24:02 -0800, "Ron Coderre"
wrote:

Try this:
B1: =COUNTIF(A:A,"YES")
B2: =COUNTIF(A:A,"NO")

Does that help?

Sure does, thank you to you and everyone for your responses! I only
just recently learned about Countif and just didn't realize it could
do something like this, too! Fabulous.

I believe I brought home a copy of the sheet in case there were
responses in this ng. If so, I'll work on it now if that's the case
so that I can re-send a new-and-improved version tomorrow. It will
mean less fiddling for the team.

Thank you!! You're all lifesavers once again! <g

***********
Regards,
Ron


"StargateFanFromWork" wrote:

I just had a rush job to do up an Excel sheet while my supervisor dictated
the data to me as she interpreted the emails/faxes with various responses.
One column in particular had YES and NO as the possible data type. In the
end, I was to total up the number of YES answers and put the total in one
cell; and then to do the same for the NO answers. These YES's and NO's are
all in one column, unfortunately, but the sum of each we put into 2 cells.
Is there a formula that I could have used for each just substituting a value
that would work with the YES answers and another that would return the total
of the NO answers?

I had a filter and because this was a rush just filtered for each and
counted them up and inserted figure manually, but it would be good to
automate this as they'll need to be inputting more data as responses come in
over the next 5 days. Hoping there's a way to deal with the two types of
data.

Thank you!! :oD

On Wed, 07 Dec 2005 19:23:58 -0600, Dave Peterson
wrote:

=countif(a:a,"yes")

would count the number of Yes's in column A.

="Total Yes's: " & countif(a:a,"yes") & char(10)
& "Total No's: " & countif(a:a,"no")

all one cell with wrap text turned on would look like this:

Total Yes's: 12
Total No's: 13

But I'd use separate cells to make any further calculation easier.

Do you mean that if there were more than 2 types of data to deal with
other than just YES or NO? Makes sense. In this application I should
be safe as it's just the 2 for now (and although management looks to
put monkey wrenches into things, can't see them doing too much more
than YES or NO in this particular instance. In this case, in other
words, so far there can only be an either/or answer. I defy them to
come up with something new for this particular application <vbg.
Although, perhaps I shouldn't hold my breath ... :oD)

StargateFanFromWork wrote:

I just had a rush job to do up an Excel sheet while my supervisor dictated
the data to me as she interpreted the emails/faxes with various responses.
One column in particular had YES and NO as the possible data type. In the
end, I was to total up the number of YES answers and put the total in one
cell; and then to do the same for the NO answers. These YES's and NO's are
all in one column, unfortunately, but the sum of each we put into 2 cells.
Is there a formula that I could have used for each just substituting a value
that would work with the YES answers and another that would return the total
of the NO answers?

I had a filter and because this was a rush just filtered for each and
counted them up and inserted figure manually, but it would be good to
automate this as they'll need to be inputting more data as responses come in
over the next 5 days. Hoping there's a way to deal with the two types of
data.

Thank you!! :oD

"StargateFan" wrote in message
...
On Wed, 7 Dec 2005 17:24:02 -0800, "Ron Coderre"
wrote:

Try this:
B1: =COUNTIF(A:A,"YES")
B2: =COUNTIF(A:A,"NO")

Does that help?

Yes. Added this this afternoon to the 2 cells and it worked perfectly!
Thanks. :oD

***********
Regards,
Ron


"StargateFanFromWork" wrote:

I just had a rush job to do up an Excel sheet while my supervisor
dictated
the data to me as she interpreted the emails/faxes with various
responses.
One column in particular had YES and NO as the possible data type. In
the
end, I was to total up the number of YES answers and put the total in
one
cell; and then to do the same for the NO answers. These YES's and NO's
are
all in one column, unfortunately, but the sum of each we put into 2
cells.
Is there a formula that I could have used for each just substituting a
value
that would work with the YES answers and another that would return the
total
of the NO answers?

I had a filter and because this was a rush just filtered for each and
counted them up and inserted figure manually, but it would be good to
automate this as they'll need to be inputting more data as responses
come in
over the next 5 days. Hoping there's a way to deal with the two types
of
data.

Thank you!! :oD