[Solved] Summing From 2 Sheets Into A 3rd Sheet

[adventurehouse]

I'm trying to produce an easy Summary of my expenditures for my accountant.

I have a spreadsheet with multiple sheets. Each sheet is an account that I need to sum.

What I'm not sure how to, or if to use SUMPRODUCT, SUMIFS or just SUM, here is what I need to do.

=SUMPRODUCT('Capital One'.E2:E647="G2"; 'Capital One'.F2:F647)

The G2 is how Identify a category that I need to account for. Each sheet has these identifiers to allow me to sum from all of them into a single summary page. So, what I need to do is combine the example from above and add =SUMPRODUCT(BOA.E2:E135="G2"; BOA.D2:D135), so the summary shows both sheets figures into a single calculation.

Thanks for the help.

[FJCC]

In the formula

Code: Select all

=SUMPRODUCT('Capital One'.E2:E647="G2"; 'Capital One'.F2:F647)

is G2 the text that appears in column E of the Capital One sheet or do you mean that to be reference to cell G2 of the sheet containing the formula? If it is a cell reference, do not put it in quotes.

Your final formula would be

Code: Select all

==SUMPRODUCT('Capital One'.E2:E647=G2; 'Capital One'.F2:F647) + SUMPRODUCT(BOA.E2:E135=G2; BOA.D2:D135)

if I am understanding you correctly.

[Villeroy]

It could be far easier if you would not store equally structured data on separate sheets.

[adventurehouse]

The different sheets are the data from different accounts and needs to be separated for the accountant to view.

G2 is not a cell, but a sample of the text id that appears within the sheet.

Thanks for the info...it works.

[RusselB]

The data could (probably) be stored on a single sheet, then use a Pivot Table (aka Data Pilot) to generate the specific information needed for the accountant when necessary.
While having the information spread out looks good from a human point of view, it can (and often does) make it much more complicated for doing calculations and/or data manipulation.

[Villeroy]

Everything becaomes easier with single sheets. Of course you keep an additional column with the account names that used to be the sheet names. instead of selecting a distinct sheet, you select an auto-filter entry from a drop-down in order to view a particular account.

Actually, accounting on spreadsheets is a very good reason to be fired. I would never accept this for my own business.