Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting columns, adding totals
Hi. I've been handed an excel file that needs tweaking. Was wondering if
someone here could help. This xls file consists of 7 worksheets, one of every day of the week. Each worksheet has a 30 column section, each marked either [X] or [ ]. There's one row for each person, the columns represent their 1/2 hour schedule. 1) I need a totals column, totaling the # of Xs in the 30 columns for that person. Is there a way to determine if a cell has an [X] or not? If so, add 1 to a count? 2) This totals column also needs to show department sub-total and grand total. Should be easy enough, but I need to print a summary version of the worksheet, showing just the department totals. 3) I figure the next thing the client will ask to a printed report showing department totals of the whole week, all 7 worksheets. Is that possible? As you may have guessed, I'm not an Excel person. First chance I get, I'll run down to B&N and find an Excel reference book. Any advice from this newsgroup will still be greatly appreciated...Dennis |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting columns, adding totals
You want to do this in a macro or using a worksheet function?
If you just want to do it in a worksheet function, then put your curso where you want the result to be and click on "Insert Function". Select the "Countif" function. The wizard will help you set up th formula. You want something like this: =COUNTIF(A1:A30,"X" -- Message posted from http://www.ExcelForum.com |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting columns, adding totals
You want to do this in a macro or using a worksheet function?
If you just want to do it in a worksheet function, then put your curso where you want the result to be and click on "Insert Function". Select the "Countif" function. The wizard will help you set up th formula. You want something like this: =COUNTIF(A1:A30,"X" -- Message posted from http://www.ExcelForum.com |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting columns, adding totals
here's a start...by the way if you ever need to see if
excel can do something try clicking the fx on the toolbar to bring up possiblr functions.... start with a countif function....countif("range","[X]") to get a count of the [X]'s where "range" is the row of entries...add another workshet bringing the totals....you can then print each sheet (fitting each one to 1 page - probably landscape).... John -----Original Message----- Hi. I've been handed an excel file that needs tweaking. Was wondering if someone here could help. This xls file consists of 7 worksheets, one of every day of the week. Each worksheet has a 30 column section, each marked either [X] or [ ]. There's one row for each person, the columns represent their 1/2 hour schedule. 1) I need a totals column, totaling the # of Xs in the 30 columns for that person. Is there a way to determine if a cell has an [X] or not? If so, add 1 to a count? 2) This totals column also needs to show department sub- total and grand total. Should be easy enough, but I need to print a summary version of the worksheet, showing just the department totals. 3) I figure the next thing the client will ask to a printed report showing department totals of the whole week, all 7 worksheets. Is that possible? As you may have guessed, I'm not an Excel person. First chance I get, I'll run down to B&N and find an Excel reference book. Any advice from this newsgroup will still be greatly appreciated...Dennis . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting columns, adding totals
Although I couldn't figure out exactly what you need, but I guess yo
require something like A B C D E 1 C1 C2 C3 C4 Total 2 X X X =COUNTIF(A2:D2,"X") 3 4 - Manges -- Message posted from http://www.ExcelForum.com |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting columns, adding totals
Thanks for the reply. Question. If I want to copy the countif() in the
same column, but different rows, is that =COUNTIF($A1:$A30,"X")? Now if I can figure out how to print a summary version of each sheet and link the numbers of each sheets to a summary sheet...Dennis "MSP77079 " wrote in message ... You want to do this in a macro or using a worksheet function? If you just want to do it in a worksheet function, then put your cursor where you want the result to be and click on "Insert Function". Select the "Countif" function. The wizard will help you set up the formula. You want something like this: =COUNTIF(A1:A30,"X") --- Message posted from http://www.ExcelForum.com/ |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting columns, adding totals
Thanks for the reply. Now if I can figure out how to print a summary
version of each sheet and link the numbers of each sheet to a summary sheet...Dennis "mangesh_yadav " wrote in message ... Although I couldn't figure out exactly what you need, but I guess you require something like A B C D E 1 C1 C2 C3 C4 Total 2 X X X =COUNTIF(A2:D2,"X") 3 4 - Mangesh --- Message posted from http://www.ExcelForum.com/ |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting columns, adding totals
Oops. $ is absolute. My mistake...Dennis
"Dennis Allen" wrote in message ... Thanks for the reply. Question. If I want to copy the countif() in the same column, but different rows, is that =COUNTIF($A1:$A30,"X")? Now if I can figure out how to print a summary version of each sheet and link the numbers of each sheets to a summary sheet...Dennis "MSP77079 " wrote in message ... You want to do this in a macro or using a worksheet function? If you just want to do it in a worksheet function, then put your cursor where you want the result to be and click on "Insert Function". Select the "Countif" function. The wizard will help you set up the formula. You want something like this: =COUNTIF(A1:A30,"X") --- Message posted from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding totals from different columns | Excel Worksheet Functions | |||
Adding totals in columns | Excel Worksheet Functions | |||
Counting totals between multiple columns for like items | Excel Worksheet Functions | |||
adding totals in a column, not counting repetitions in another col | Excel Discussion (Misc queries) | |||
Counting (sub)totals | Excel Worksheet Functions |