Summarizing data into different worksheet


L

Liztd

I need to report figures from different worksheets into a summary tab within
the same workbook (i.e. data from Jan, Feb, Mar tabs into 1 'Summary' tab).

The figures need to be summed and are dependent on product codes from a
spreadsheet for each month containing data of expenses:

E.g.
Code | Amount
P01 | 100
P02 | 200
P01 | 300

I need to summarise all the P01, P02, amounts into their relevant cells on a
summary worksheet.

E.g.
PO1 | Jan Amount | Feb Amount
PO2 | Jan Amount | Feb Amount

Could anyone please let me know how I can do this?

I've tried SUMIF and populating from a pivot table although the pivot table
contents may change month by month but both of these methods have failed.

With SUMIF it hasn't picked up the Amount column to summarise.
E.g. SUMIF(Jan_data, "P01", Jan Expenses!D$D)
...where D is the column containing the amounts.

Eventually I might like to add additional conditions to fulfill but for the
moment any sums would be good!

Any ideas would be gratefully appreciated.

Thanks
Liz
 
Ad

Advertisements

V

vezerid

Say your summary sheet, B1:M1 contain the names Jan, Feb etc, in
exactly the same manner they appear as sheet names. A2:A101 contain
P01 etc. The amounts to be summed are in column D:D of each sheet. In
B2:

=SUMPRODUCT(INDIRECT("'"&B$1&"'!D2:D101")*(INDIRECT("'"&B$1&"'!
A2:A101")=$A2))

Adjust the A2:A101, D2:D101 to suit.
If your sheets are not called 'Jan' etc but rather 'Jan Expenses' then
modify the formula to:

=SUMPRODUCT(INDIRECT("'"&B$1&" Expenses'!D2:D101")*(INDIRECT("'"&B$1&"
Expenses'!A2:A101")=$A2))

Does this help?
Kostis Vezerides
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top