If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 - |
Thread Tools | |
Display Modes | |
|
|