multiple worksheets

[chieflx]

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

 

[Pete_UK]

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

 

[chieflx]

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]

You're welcome, Rob - thanks for feeding back.

Pete