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é

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

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é

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é