Summing from multiple worksheets

[Bill]

Background. windows XP home Excel 97
I have a workbook with lots of worksheets, each relating
to flight performance measurement against an Service
Level Agreement (SLA).
Each worksheet has its own totals
I have one worksheet within that work book which will
summarise the "Total" figures in all the other
worksheets.
From this I can create a PIVOT.
Question.
Is there an easier way to capture each worksheet's totals
in the Summary worksheet Total other than =sum
(copy>pastelink) from each Total cell in each worksheet?
Doing it this way works but is time consuming
Suggestions woulod be gratefully received

 

[Ron de Bruin]

Try this if the totals are in the same cell in each sheet

Add two dummy sheets(empty sheets)with the name start as the first sheet
and one with the name end as the last sheet of your workbook.
this are empty sheets!!!

All worksheets between these sheets will be sum
with this formula

=SUM(start:end!B2)

 

[Bill]

Thanks very much. Achieved what I wanted
Bill

-----Original Message-----
Try this if the totals are in the same cell in each sheet

Add two dummy sheets(empty sheets)with the name start as the first sheet
and one with the name end as the last sheet of your workbook.
this are empty sheets!!!

All worksheets between these sheets will be sum
with this formula

=SUM(start:end!B2)


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Bill" <[email protected]> wrote in

message news:[email protected]...

 

[wolfpack95]

Same situation but using a COUNTIF instead of a SUM. Why isn't it
working?

I have text in A2 and I want to count the number of times it shows up
in K5 on worksheets 1-32. The sum example seems to work but my countif
is not. I have verified that the text is exactly the same by using
another formula to test:

Text test
"=IF(A2='1'!$K$5, "Equal", "Not" and this returns "Equal").

CountIF that's not working
"=COUNTIF(A2,'1:32'!$K$5)" returns 0 (ie no matches)

Please help and thanks in advance.

 

[Frank Kabel]

Hi
try
=SUMPRODUCT(COUNTIF(INDIRECT("'" & ROW(1:32) & "'!K5"),A2))

 

[wolfpack95]

Frank, thanks for the reply but that formula gives me a #REF! error.

Also, I don't quite understand the code but it looks like the ROW
function is looking at rows 1-32 in the first worksheet only. I'm
trying to count if a value is found in cell K5 on 32 different
worksheets (1-32).

Can you straighten me out? Thanks again.

 

[Frank Kabel]

Hi
I assumed your worksheets were name '1' to '32'. So this was that the
ROW() parts was for. If you have other names try the following:
- put all your names in a range of your worksheet. e.g. in cells X1:X32
- now use the formula
=SUMPRODUCT(COUNTIF(INDIRECT("'" & X1:X32 & "'!K5"),A2))

 

[wolfpack95]

Thanks Frank