[Solved] SUMIF across multiple sheets

[BFras914]

I've searched all over the forum and can't find a topic that matches what I'm trying to do. I apologize if this has been covered elsewhere.

I have a Calc file with one sheet for each week of the year. Each of these 52 sheets contains sales data for several thousand items. For simplicity's sake, I'll focus on the two relevant columns: column A contains an item number, and column B contains that item's sales totals for the week.

I also have an Annual Totals sheet, on which Column A contains every unique item number that occurs on any of the 52 weekly sheets, and I want Column B to add up all sales totals for that item number from the weekly sheets.

This would be easy if every sheet contained the same items, as I could use a formula like SUM(Wk1.B2:Wk52.B2). But my challenge is that not every item is sold every week. I import the weekly data from a report that only lists items with sales, so I can't have each week's sheet set up exatly the same, which would allow me to use that type of SUM formula.

I'm trying to figure out if a similar formula can be done using SUMIF (or some other function). Basically, I want a formula in Column B on my Totals sheet that will look for the value in column A and add up the totals from Column B on all 52 weekly sheets for each occurrence of that value. I know I could accomplish this goal with something like:

=SUMIF(Wk1.A2:A5000;A2;Wk1.B2:B500)+SUMIF(Wk2.A2:A5000;A2;Wk2.B2:B500)...+SUMIF(Wk52.A2:A5000;A2;Wk52.B2:B500)

But I really don't want to type out that whole formula, and it's probably over the character limit for a formula anyway.

The only other option I can think of right now is to combine all of my weekly data into one sheet and then use a SUMIF to pull the totals from that sheet. But I need to keep a separate sheet for each week, so I don't want to combine all of that detail into one sheet if it's not necessary.

Thanks in advance for any advice!

[keme]

The best way would be to use a database, but I appreciate the simplicity and freedom of using a spreadsheet.

The second best way, as you say, would be to have everything in one sheet. You could use a data filter to select only a specific week's data. However you have already set up your system with one sheet for each week, so I guess that's how it will be.

The next possibility is to have one SUMIF() on each weekly sheet. If you create it on one sheet, using absolute addressing for the test data, you can copy that to all the other weekly sheets. Have it in the same position on every sheet, and you can use the SUM() across sheets that you suggested.

[BFras914]

That last suggestion is probably the best option for the way I have this setup. I hadn't thought of that, but I think it might work. Thanks for the idea!

When I started gathering data for this particular project, no one knew exactly what they wanted the end result to look like. So unfortunately I did a lot of my spreadsheet setup and formatting before we had a reall end result in mind. So rather than completely starting from scratch, I'm just trying to find the best way to minimize the work required when we update the data every week in the future. I would definitely have done it differently if I'd known in advance what we were ultimately trying to get to.

[Villeroy]

menu:Data>Consolidation can aggregate (sum, count, average,...) equally structured data from more than one list.
In the dialog you specify the source lists and how to aggregate them (e.g. group by by persons and sum amounts for persons).
I never used that feature. Database are much easier to use (but not as easy to create though).

[mriosv]

Find attached a file with an array formula to sum from several sheets.
Needs a structured name of sheet to sum ending in consecutive numbers, for example: Week1 Week2 ... Week52
And adapt de formula to the specific situation. Number of sheets to sum [COLUMN(A$1:E$1)], and max number of row [ROW(A$1:A$500)] with data in the sheets to sum.
=SUM((INDIRECT("Week"&COLUMN(A$1:E$1)&".A"&ROW(A$1:A$500))=$A2)*INDIRECT("Week"&COLUMN(A$1:E$1)&".B"&ROW(A$1:A$500)))
Use Ctrl+Enter to introduce the formula.

[Alex1]

52 times several thousand items is probably more than 65536, the number of rows Calc can handle, but version 3.3, which is expected soon, can handle more rows. Then you can put all sales on one sheet, and use the Data Pilot to calculate the sales totals, without entering any formula. Attached is an example. The totals table on the second sheet is created using the Data Pilot: select columns A to C on the first sheet, click Data, Data Pilot, Start, choose Current selection, Ok, drag the Items button to Row fields, and the Sales button to the Data field. Under More, select Results to new sheet, check Ignore empty rows, Ok. The weeks column is optional.