Hi Biff,
I have ended up putting the scenario also under Live Meeting titled Excel
Worksheet Functions dated 15/05/05. I have given a more complete example of
what my situation is.
Sheets 1 - 4 are the 1/3rd period sheets and have the client name in column
B and the total for the 1/3rd periods worth of payments is in column M.
I want my summary sheet no. 5 to have six columns, being (A) for the clients
name, (B) for the 1st 3rd total, (C) for the 2nd 3rd total, (D) for the third
3rd total, (E) for the 4th 3rd total and (F) for the total for the year
summed from columns (B) - (E). I need all the 1/3rd periods showing
individually on the summary sheet.
Hope this helps.
Thanks Sue

"Biff" wrote:

Hi!

On your sheets -

Sheet 1 is 1st 3rd, sheet 2 is 2nd 3rd, sheet 3 is 3rd 3rd, sheet 4 is 4th
3rd

Where are the client names and where is the data you want returned?

For example:

The client names are in column A and the sales amount to return is in column
C.

Biff

"Sue" wrote in message
...
Hi,
I am still having problems. I have tried both suggestions and I cannot
seem
to get either one to work. Both come up with incorrect formula. I am
wanting
my summary sheet to show each of the 1/3rd totals for each client and then
I
will sum those figures. Summary sheet will contain 6 columns as follows:
Name, 1st 3rd, 2nd 3rd, 3rd 3rd, 4th 3rd and then the total column for the
year bearing in mind that the clients are in different spots on the
individual sheets for each 3rd.
Sheet 1 is 1st 3rd, sheet 2 is 2nd 3rd, sheet 3 is 3rd 3rd, sheet 4 is 4th
3rd and sheet 5 is the summary sheet. This summary sheet will show all
clients active during the year whether they are with us as at the end of
the
year or not. The sheets 1 - 4 show only clients that are active during
that
particular 3rd. Am I asking the impossible?

"Biff" wrote:

Hi!

Assume you want to total sales amounts for client SMITH from all of your
sheets. All the sheets have the identical format in that client names are
in
column A and the sales figures to sum are in column B.

If you only have 2 or 3 sheets you may want to use this formula:

=SUMIF(Sheet2!A1:A100,A1,Sheet2!B1:B100)+SUMIF(She et3!A1:A100,A1,Sheet3!B1:B100)

Where A1 is the clients name.

If you have more than 2 or 3 sheets:

Make a list of the sheet names somewhere, say, D1:D10. Then use this
formula:

=SUMPRODUCT(SUMIF(INDIRECT("'"&D1:D10&"'!A1:A100") ,A1,INDIRECT("'"&D1:D10&"'!B1:B100")))

Biff

"Sue" wrote in message
...
Can excel pick up totals on other sheets and workbooks for clients,
where
the
clients information is never on the same row no. on each sheet, without
it
being a lengthy case of having to alter the formula to accomodate the
cell
change address? In other words how do I get it to recognise the name
rather
than the cell for picking up the info.?
The worksheets show money coming in during each 3rd of the year from
clients
and the total. On a summary sheet I want excel to pick up those 1/3rd
totals
for each client so I can then total those to give the yearly figure for
each
client. The reason the cell address is not consistent on each 1/3rd
period
sheet is because each client's time span with us is only ever a maximum
of
2
years but usually less. So that means the current clients move up the
row
on
the sheet as other clients finish hence the different row numbers.