A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

multiple worksheets



 
 
Thread Tools Display Modes
  #1  
Old March 18th, 2010, 05:13 PM posted to microsoft.public.excel.worksheet.functions
chieflx
external usenet poster
 
Posts: 16
Default 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  
Old March 18th, 2010, 06:05 PM posted to microsoft.public.excel.worksheet.functions
Pete_UK
external usenet poster
 
Posts: 8,780
Default 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  
Old March 18th, 2010, 10:35 PM posted to microsoft.public.excel.worksheet.functions
chieflx
external usenet poster
 
Posts: 16
Default 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  
Old March 20th, 2010, 03:39 AM posted to microsoft.public.excel.worksheet.functions
Pete_UK
external usenet poster
 
Posts: 8,780
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 02:43 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.