Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum several sheets
Hi,
I need a formula that do the same as =SUM(A:B!B5) which sums all sheets between A and B. My problem is that the cells to include in the summary, isn't on a fixed place. In some sheets the value can be in B6 and the rest could be B5. I have tried SUMIF and array formulas, but I don't get Excel to understand where to look... Any tips? Is it even possible? BR André |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum several sheets
Hi André
How, specifically, have you tried SUMIF and array formulas? What's the difference that puts the value in either B6 or B5? Dan |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum several sheets
"dan dungan" skrev i melding
... Hi André How, specifically, have you tried SUMIF and array formulas? What's the difference that puts the value in either B6 or B5? Dan Hi, My table looks like this: ColumnA Column B Account 1 SUM ALL AMOUNTS THAT HAS ACCOUNT 1 IN COLUMN A IN SHEETS BETWEEN A AND B Account 2 SUM ALL AMOUNTS THAT HAS ACCOUNT 2 IN COLUMN A IN SHEETS BETWEEN A AND B Account 3 SUM ALL AMOUNTS THAT HAS ACCOUNT 3 IN COLUMN A IN SHEETS BETWEEN A AND B Normally one would use =SUMIF(A3:A100;A3;B3:B100) in cell B3 etc.... But since I have the values in multiple sheets, I don't manage to do what I want. In the sheets between A and B, the accountnumber isn't on the same cell in all sheets. In some sheets, the account 1 could be 3 lines below the "standard". If the cells has been on the excact same place in all sheets, I simply could use =SUM(A:B!B3) André |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum several sheets
One way...
List the sheet names that you want to include in the calculation in a range of cells. Assume this range is J1:J10 =SUMPRODUCT(SUMIF(INDIRECT("'"&J1:J10&"'!A3:A100") ,A3,INDIRECT("'"&J1:J10&"'!B3:B100"))) -- Biff Microsoft Excel MVP "André" wrote in message ... "dan dungan" skrev i melding ... Hi André How, specifically, have you tried SUMIF and array formulas? What's the difference that puts the value in either B6 or B5? Dan Hi, My table looks like this: ColumnA Column B Account 1 SUM ALL AMOUNTS THAT HAS ACCOUNT 1 IN COLUMN A IN SHEETS BETWEEN A AND B Account 2 SUM ALL AMOUNTS THAT HAS ACCOUNT 2 IN COLUMN A IN SHEETS BETWEEN A AND B Account 3 SUM ALL AMOUNTS THAT HAS ACCOUNT 3 IN COLUMN A IN SHEETS BETWEEN A AND B Normally one would use =SUMIF(A3:A100;A3;B3:B100) in cell B3 etc.... But since I have the values in multiple sheets, I don't manage to do what I want. In the sheets between A and B, the accountnumber isn't on the same cell in all sheets. In some sheets, the account 1 could be 3 lines below the "standard". If the cells has been on the excact same place in all sheets, I simply could use =SUM(A:B!B3) André |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I print sheets in Excel without blank sheets after page | New Users to Excel | |||
Multiple Sheets (Need to create 500 individual sheets in one workbook, pulling DATA | Excel Worksheet Functions | |||
How to repeat a code for selected sheets (or a contiguous range of sheets) in a Workbook? | Excel Worksheet Functions | |||
In 3 active sheets in wkbk, determine& display the # of sheets that have data | Excel Discussion (Misc queries) | |||
populating sheets based on data from parent sheets | Excel Discussion (Misc queries) |