Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
multiple worksheets
Hi,
I wonder if anyone might be able to give me a bit of advice. I have several worksheets containing observation data from different days, all contained in one workbook. I have assigned a numeric value to each state i.e. left=1, right=2. I have four states and each time I collect data it goes on to a dated (via the tab) worksheet. I want to total the number of each category across the whole observation period. I have tried using the following formula =COUNTIFS('7 Dec'!F8:J15,1,'11 Jan'!F8:J15,1,'18 Jan'!F8:J14,1,'8 March'!F8:J14,1) but it doesn't seem to work. I am using excel 2007 but I also need it to work on excel 2003. it works if I only want one worksheet but I need to add all occurrences of each state. Any help would be gratefully appreciated. Al the best, Rob R |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
multiple worksheets
Well, if you want it to work in XL2003 you won't be able to use
COUNTIFS (but you say that doesn't work, anyway) COUNTIF (and others) is not very good across worksheets. I suggest you have a COUNTIF in the same cell of each sheet, eg in X1: =COUNTIF(F8:J15,1) with similar formulae in X2:X4 for the other states. Then in your summary sheet you can just add these up: =SUM(first:last!X1) where first is the name of the first sheet and last is the last sheet in sequence that you want to add from. In fact, you can set up two dummy sheets with no data on called first and last and just position these so that they encompass all the sheets that you want to add from (rather like a sandwich). Then you can just move new sheets into or out of this sandwich without having to change the formula. Obviously, your summary sheet needs to be outside the sandwich. You can copy that formula down to pick up the totals from X2 to X4 in the other sheets. Hope this helps. Pete On Mar 18, 4:13*pm, chieflx wrote: Hi, I wonder if anyone might be able to give me a bit of advice. *I have several worksheets containing observation data from different days, all contained in one workbook. *I have assigned a numeric value to each state i.e. left=1, right=2. I have four states and each time I collect data it goes on to a dated (via the tab) worksheet. *I want to total the number of each category across the whole observation period. *I have tried using the following formula =COUNTIFS('7 Dec'!F8:J15,1,'11 Jan'!F8:J15,1,'18 Jan'!F8:J14,1,'8 March'!F8:J14,1) but it doesn't seem to work. *I am using excel 2007 but I also need it to work on excel 2003. *it works if I only want one worksheet but I need to add all occurrences of each state. Any help would be gratefully appreciated. Al the best, Rob R |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
multiple worksheets
hi Pete,
Thanks very much for that solution. I have just tried it and it works very well, in fact having individual totals for each day makes more sense so you have help improve my data as well. Once again thanks again Rob R "Pete_UK" wrote: Well, if you want it to work in XL2003 you won't be able to use COUNTIFS (but you say that doesn't work, anyway) COUNTIF (and others) is not very good across worksheets. I suggest you have a COUNTIF in the same cell of each sheet, eg in X1: =COUNTIF(F8:J15,1) with similar formulae in X2:X4 for the other states. Then in your summary sheet you can just add these up: =SUM(first:last!X1) where first is the name of the first sheet and last is the last sheet in sequence that you want to add from. In fact, you can set up two dummy sheets with no data on called first and last and just position these so that they encompass all the sheets that you want to add from (rather like a sandwich). Then you can just move new sheets into or out of this sandwich without having to change the formula. Obviously, your summary sheet needs to be outside the sandwich. You can copy that formula down to pick up the totals from X2 to X4 in the other sheets. Hope this helps. Pete On Mar 18, 4:13 pm, chieflx wrote: Hi, I wonder if anyone might be able to give me a bit of advice. I have several worksheets containing observation data from different days, all contained in one workbook. I have assigned a numeric value to each state i.e. left=1, right=2. I have four states and each time I collect data it goes on to a dated (via the tab) worksheet. I want to total the number of each category across the whole observation period. I have tried using the following formula =COUNTIFS('7 Dec'!F8:J15,1,'11 Jan'!F8:J15,1,'18 Jan'!F8:J14,1,'8 March'!F8:J14,1) but it doesn't seem to work. I am using excel 2007 but I also need it to work on excel 2003. it works if I only want one worksheet but I need to add all occurrences of each state. Any help would be gratefully appreciated. Al the best, Rob R . |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
multiple worksheets
You're welcome, Rob - thanks for feeding back.
Pete On Mar 18, 9:35*pm, chieflx wrote: hi Pete, Thanks very much for that solution. *I have just tried it and it works very well, in fact having individual totals for each day makes more sense so you have help improve my data as well. Once again thanks again Rob R "Pete_UK" wrote: Well, if you want it to work in XL2003 you won't be able to use COUNTIFS (but you say that doesn't work, anyway) COUNTIF (and others) is not very good across worksheets. I suggest you have a COUNTIF in the same cell of each sheet, eg in X1: =COUNTIF(F8:J15,1) with similar formulae in X2:X4 for the other states. Then in your summary sheet you can just add these up: =SUM(first:last!X1) where first is the name of the first sheet and last is the last sheet in sequence that you want to add from. In fact, you can set up two dummy sheets with no data on called first and last and just position these so that they encompass all the sheets that you want to add from (rather like a sandwich). Then you can just move new sheets into or out of this sandwich without having to change the formula. Obviously, your summary sheet needs to be outside the sandwich. You can copy that formula down to pick up the totals from X2 to X4 in the other sheets. Hope this helps. Pete On Mar 18, 4:13 pm, chieflx wrote: Hi, I wonder if anyone might be able to give me a bit of advice. *I have several worksheets containing observation data from different days, all contained in one workbook. *I have assigned a numeric value to each state i.e. left=1, right=2. I have four states and each time I collect data it goes on to a dated (via the tab) worksheet. *I want to total the number of each category across the whole observation period. *I have tried using the following formula =COUNTIFS('7 Dec'!F8:J15,1,'11 Jan'!F8:J15,1,'18 Jan'!F8:J14,1,'8 March'!F8:J14,1) but it doesn't seem to work. *I am using excel 2007 but I also need it to work on excel 2003. *it works if I only want one worksheet but I need to add all occurrences of each state. Any help would be gratefully appreciated. Al the best, Rob R .- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
merge multiple worksheets from multiple excel files into oneworksheet | Excel Discussion (Misc queries) | |||
SUMPRODUCT / SUMIF on Multiple Worksheets with Multiple Criteria | Excel Discussion (Misc queries) | |||
Need Simple List...Multiple Columns and Multiple Worksheets | Excel Worksheet Functions | |||
how to make a macro to clear multiple cells from multiple worksheets? | Excel Worksheet Functions | |||
Delete Blank Rows Code - Multiple Worksheets - Multiple Documents | Excel Discussion (Misc queries) |