Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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 |
#8
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sorting Spreadsheet with Merged Fields | Excel Discussion (Misc queries) | |||
named range refers to: in a chart | Excel Discussion (Misc queries) | |||
Counting values within a Date Range | Excel Discussion (Misc queries) | |||
range name | Excel Worksheet Functions | |||
Counting Function Dilemma | Excel Worksheet Functions |