Counting a range of fields with an "X"
 

I am setting up a very simple spread sheet with a work schedule for
volunteers.

In Fields A-4 through A-24, I have their names. In fields B-4 to A-24 I
have an X showing they are working that date. These columns are repeated
all the way though T-4 to T-24.

What I am trying to do is have a count of the X's by column at the bottom of
each column.

I have tried several different formulas, but keep receiving the error
message of the formula you typed contains an error..

I have been reading the newsgroup to see if it had been covered, but could
not find anything on it. Of course I could have followed the newsgroup for
more than a day and maybe stumbled across it.

Any suggestions would be appreciated
Bob

Hi Bob

You can use this function
=COUNTIF(B1:B24,"x")

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Arge" wrote in message ...
I am setting up a very simple spread sheet with a work schedule for volunteers.

In Fields A-4 through A-24, I have their names. In fields B-4 to A-24 I have an X showing they are working that date. These
columns are repeated all the way though T-4 to T-24.

What I am trying to do is have a count of the X's by column at the bottom of each column.

I have tried several different formulas, but keep receiving the error message of the formula you typed contains an error..

I have been reading the newsgroup to see if it had been covered, but could not find anything on it. Of course I could have
followed the newsgroup for more than a day and maybe stumbled across it.

Any suggestions would be appreciated
Bob

Hi
=COUNTIF(A1:A100,"X")

--
Regards
Frank Kabel
Frankfurt, Germany

"Arge" schrieb im Newsbeitrag
...
I am setting up a very simple spread sheet with a work schedule for
volunteers.

In Fields A-4 through A-24, I have their names. In fields B-4 to
A-24 I
have an X showing they are working that date. These columns are
repeated
all the way though T-4 to T-24.

What I am trying to do is have a count of the X's by column at the
bottom of
each column.

I have tried several different formulas, but keep receiving the error
message of the formula you typed contains an error..

I have been reading the newsgroup to see if it had been covered, but
could
not find anything on it. Of course I could have followed the
newsgroup for
more than a day and maybe stumbled across it.

Any suggestions would be appreciated
Bob

Arge, try something like this
=COUNTIF(B1:B24,"x")
--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

"Arge" wrote in message
...
I am setting up a very simple spread sheet with a work schedule for
volunteers.

In Fields A-4 through A-24, I have their names. In fields B-4 to A-24 I
have an X showing they are working that date. These columns are repeated
all the way though T-4 to T-24.

What I am trying to do is have a count of the X's by column at the bottom
of each column.

I have tried several different formulas, but keep receiving the error
message of the formula you typed contains an error..

I have been reading the newsgroup to see if it had been covered, but could
not find anything on it. Of course I could have followed the newsgroup
for more than a day and maybe stumbled across it.

Any suggestions would be appreciated
Bob

On Mon, 6 Dec 2004 13:10:08 -0500, "Arge" wrote:

I am setting up a very simple spread sheet with a work schedule for
volunteers.

In Fields A-4 through A-24, I have their names. In fields B-4 to A-24 I
have an X showing they are working that date. These columns are repeated
all the way though T-4 to T-24.

What I am trying to do is have a count of the X's by column at the bottom of
each column.

I have tried several different formulas, but keep receiving the error
message of the formula you typed contains an error..

I have been reading the newsgroup to see if it had been covered, but could
not find anything on it. Of course I could have followed the newsgroup for
more than a day and maybe stumbled across it.

Any suggestions would be appreciated
Bob


If the cells in the column can contain only "X" or be blank, then
=COUNTA(B4:B24) will count the X's.

If there are non-X entries in those cells that you need to exclude, then use:

=COUNTIF(B4:B24, "X")


--ron

Hi Ron de Bruin,Frank Kabel, Paul B and Ron Rosenfeld

Thanks to all of you for such a quick response. I had tried the COUNTIF
formula but I was not entering the X the way that was suggested. I had not
tried the COUNTA formula though. However when using both formulas if I have
3 X in the column, the answer at the bottom shows 1/3. If 4 X then it shows
1/4. I am not understanding where the 1 is coming from or now to delete it.

Again thank for the response

Bob
"Ron Rosenfeld" wrote in message
...
On Mon, 6 Dec 2004 13:10:08 -0500, "Arge"
wrote:

I am setting up a very simple spread sheet with a work schedule for
volunteers.

In Fields A-4 through A-24, I have their names. In fields B-4 to A-24 I
have an X showing they are working that date. These columns are repeated
all the way though T-4 to T-24.

What I am trying to do is have a count of the X's by column at the bottom
of
each column.

I have tried several different formulas, but keep receiving the error
message of the formula you typed contains an error..

I have been reading the newsgroup to see if it had been covered, but could
not find anything on it. Of course I could have followed the newsgroup
for
more than a day and maybe stumbled across it.

Any suggestions would be appreciated
Bob


If the cells in the column can contain only "X" or be blank, then
=COUNTA(B4:B24) will count the X's.

If there are non-X entries in those cells that you need to exclude, then
use:

=COUNTIF(B4:B24, "X")


--ron

I'd double check the formula first.

If that's correct, try checking the number format:

Select the cell
format|cells|number tab
try changing to General
(if it's anything weird!)

Arge wrote:

Hi Ron de Bruin,Frank Kabel, Paul B and Ron Rosenfeld

Thanks to all of you for such a quick response. I had tried the COUNTIF
formula but I was not entering the X the way that was suggested. I had not
tried the COUNTA formula though. However when using both formulas if I have
3 X in the column, the answer at the bottom shows 1/3. If 4 X then it shows
1/4. I am not understanding where the 1 is coming from or now to delete it.

Again thank for the response

Bob
"Ron Rosenfeld" wrote in message
...
On Mon, 6 Dec 2004 13:10:08 -0500, "Arge"
wrote:

I am setting up a very simple spread sheet with a work schedule for
volunteers.

In Fields A-4 through A-24, I have their names. In fields B-4 to A-24 I
have an X showing they are working that date. These columns are repeated
all the way though T-4 to T-24.

What I am trying to do is have a count of the X's by column at the bottom
of
each column.

I have tried several different formulas, but keep receiving the error
message of the formula you typed contains an error..

I have been reading the newsgroup to see if it had been covered, but could
not find anything on it. Of course I could have followed the newsgroup
for
more than a day and maybe stumbled across it.

Any suggestions would be appreciated
Bob


If the cells in the column can contain only "X" or be blank, then
=COUNTA(B4:B24) will count the X's.

If there are non-X entries in those cells that you need to exclude, then
use:

=COUNTIF(B4:B24, "X")


--ron

--

Dave Peterson

Hi Dave

That resolved the problem.. When I set it to General it corrected the
problem. On the top of the chart I have the date using the "3/4" format. I
guess I didn't realize I had set the whole column that way.

Again thank for the response.

Bob

----- Original Message -----
From: "Dave Peterson"
Newsgroups: microsoft.public.excel.worksheet.functions
Sent: Monday, December 06, 2004 9:51 PM
Subject: Counting a range of fields with an "X"


I'd double check the formula first.

If that's correct, try checking the number format:

Select the cell
format|cells|number tab
try changing to General
(if it's anything weird!)

Arge wrote:

Hi Ron de Bruin,Frank Kabel, Paul B and Ron Rosenfeld

Thanks to all of you for such a quick response. I had tried the COUNTIF
formula but I was not entering the X the way that was suggested. I had
not
tried the COUNTA formula though. However when using both formulas if I
have
3 X in the column, the answer at the bottom shows 1/3. If 4 X then it
shows
1/4. I am not understanding where the 1 is coming from or now to delete
it.

Again thank for the response

Bob
"Ron Rosenfeld" wrote in message
...
On Mon, 6 Dec 2004 13:10:08 -0500, "Arge"
wrote:

I am setting up a very simple spread sheet with a work schedule for
volunteers.

In Fields A-4 through A-24, I have their names. In fields B-4 to A-24
I
have an X showing they are working that date. These columns are
repeated
all the way though T-4 to T-24.

What I am trying to do is have a count of the X's by column at the
bottom
of
each column.

I have tried several different formulas, but keep receiving the error
message of the formula you typed contains an error..

I have been reading the newsgroup to see if it had been covered, but
could
not find anything on it. Of course I could have followed the newsgroup
for
more than a day and maybe stumbled across it.

Any suggestions would be appreciated
Bob


If the cells in the column can contain only "X" or be blank, then
=COUNTA(B4:B24) will count the X's.

If there are non-X entries in those cells that you need to exclude,
then
use:

=COUNTIF(B4:B24, "X")


--ron

--

Dave Peterson


"Dave Peterson" wrote in message
...
I'd double check the formula first.

If that's correct, try checking the number format:

Select the cell
format|cells|number tab
try changing to General
(if it's anything weird!)

Arge wrote:

Hi Ron de Bruin,Frank Kabel, Paul B and Ron Rosenfeld

Thanks to all of you for such a quick response. I had tried the COUNTIF
formula but I was not entering the X the way that was suggested. I had
not
tried the COUNTA formula though. However when using both formulas if I
have
3 X in the column, the answer at the bottom shows 1/3. If 4 X then it
shows
1/4. I am not understanding where the 1 is coming from or now to delete
it.

Again thank for the response

Bob
"Ron Rosenfeld" wrote in message
...
On Mon, 6 Dec 2004 13:10:08 -0500, "Arge"
wrote:

I am setting up a very simple spread sheet with a work schedule for
volunteers.

In Fields A-4 through A-24, I have their names. In fields B-4 to A-24
I
have an X showing they are working that date. These columns are
repeated
all the way though T-4 to T-24.

What I am trying to do is have a count of the X's by column at the
bottom
of
each column.

I have tried several different formulas, but keep receiving the error
message of the formula you typed contains an error..

I have been reading the newsgroup to see if it had been covered, but
could
not find anything on it. Of course I could have followed the newsgroup
for
more than a day and maybe stumbled across it.

Any suggestions would be appreciated
Bob


If the cells in the column can contain only "X" or be blank, then
=COUNTA(B4:B24) will count the X's.

If there are non-X entries in those cells that you need to exclude,
then
use:

=COUNTIF(B4:B24, "X")


--ron

--

Dave Peterson