Create a reference to the same cell range on multiple worksheets

Will try to explain as best i can. I have an spreadsheet (2010) with multi tabs for different clients, all colums in every tab are the same so will hold similar information.

Im trying to find out a formula that will search through all tabs in the same columns to give me a value. There are 8 tabs (8 clients)

Basically im trying to find out the value of the numbers of apples bought in january from all clients.

I have this formula - =SUMIFS('Client 1:Client 8'!L:L,''Client 1:Client 8'!N:N,"January",''Client 1:Client 8'!J:J,"apples") Buts its not giving me a value

Column L:L is the value
Column N:N is the Month
Column J:J is the item i.e apples

The formula works when i do separate formulas for each tab - =SUMIFS('Client 1'!L:L,'Client 1'!N:N,"January",'Client 1'!J:J,"apples") I get a total value for that but its not working when i select all tabs... can someone help?

Thanks

Hi,

One way would be to ensure that the SUMIFS formula is in the same cell on each sheet. Then a summary formula would be

Formula:

Please Login or Register  to view this content.

However why are you creating separate sheets for each client?
Whenever I see references to needing different sheets for different clients/weeks/departments/months...etc. alarm bells start ringing. 90% of the time this is the wrong way to hold data. For any serious analysis or management information you should hold data in a normalised two dimensional table of column field labels and row records. It's then much easier to analysis with regular filtering functionality or Pivot Tables.

I suggest you keep all your data on a single sheet and add an extra column tp hold the client reference. Upload the workbook if you want some more help

IO have to agree with Richard regarding the layout of "database" type data like yours, it makes summaries SO much easier