Summing Sheets - SUMIF ?

[Steve W]

I have a workbook where I want to be able to exclude the cells in the
summing of one or more sheets based on some logic.

The solution needs to be a dynamic formula based on a flag such as Yes or No
(include or exclude the sheet)


eg how could I vary the formula below if I didnt want to get the values of
A1 from Sheet5 and Sheet9 in the summed total

=SUM(Sheet2:Sheet11!A1)


is there some smart variation of SUMIF that could apply that would refer to
say a table of Sheet names with a Yes / No flag next to each Sheet name?



Steve W

 

[Max]

One approx way might be to set it up along these lines:

Listed across in B1:F1 are
the sheetnames: Sheet1, Sheet2 ... Sheet5

In the row beneath the sheetnames, in B2:F2
are data validations (DVs) to select "Yes" or "No"

To set up the DVs, just select the range B2:F2,
Click Data > Validation, select List (under "Allow")
Put in the source box: Yes, No
Click OK

Listed in cell A3 down would be the cell references
to be summed in the sheets, e.g.: A1, A2, etc

Put in B3: =IF(B2="Yes",INDIRECT(B1&"!"&$A3),"")
Copy across to F3

Put in G3: =SUM(B3:F3)

Select B3:G3, fill down

Cols B to F will extract the values for the cell refs in col A
from the sheets flagged "Yes" in B2:F2
and Col G will give the required sums of these values

 

[Max]

Sorry, correction* for formula in B3 in:

Put in B3: =IF(B2="Yes",INDIRECT(B1&"!"&$A3),"")
Copy across to F3

Formula should read as:
Put in B3: =IF(B$2="Yes",INDIRECT(B$1&"!"&$A3),"")

*cell refs B2 and B1 corrected to enable copy down later

 

[Domenic]

Assuming that your sheets are named Sheet1, Sheet2, etc., and that
B1:B10 contains your sheet names, and C1:C10 contains the corresponding
flag, Yes/No, try the following formula...

=SUM(SUMIF(INDIRECT("'Sheet"&SMALL(IF(C1:C10="Yes",ROW(C1:C10)),ROW(INDIR
ECT("1:"&COUNTIF(C1:C10,"Yes"))))&"'!A1"),"<>"))

Hope this helps!

 

[Domenic]

Assuming that your sheets are named Sheet1, Sheet2, etc., and that
B1:B10 contains your sheet names, and C1:C10 contains the corresponding
flag, Yes/No, try the following formula...

=SUM(SUMIF(INDIRECT("'Sheet"&SMALL(IF(C1:C10="Yes",ROW(C1:C10)),ROW(INDIR
ECT("1:"&COUNTIF(C1:C10,"Yes"))))&"'!A1"),"<>"))

Hope this helps!

I forgot to mention that the formula must be confirmed with
CONTROL+SHIFT+ENTER, and not just ENTER.

Hope this helps!

 

[Domenic]

If your sheets are in fact not named Sheet1, Sheet2, etc., try the
following instead...

=SUM(SUMIF(INDIRECT("'"&T(OFFSET(B1,SMALL(IF(C1:C10="Yes",ROW(C1:C10)),RO
W(INDIRECT("1:"&D1)))-CELL("row",E1),0))&"'!A1"),"<>"))

....confirmed with CONTROL+SHIFT+ENTER, where B1:B10 contains your sheet
names, C1:C10 contains your flag, Yes/No, and D1 contains the following
formula...

=COUNTIF(C1:C10,"Yes")

Hope this helps!

 

[Steve W]

Can see how this works really well BUT, what if you wanted to apply this
formula to all possible cells in your specified worksheet array rather than
just A1.
How would/could you alter the formula to allow the A1 reference at the end
to become dynamic when copied.

Steve

 

[Domenic]

Try the following...

=SUM(SUMIF(INDIRECT("'"&T(OFFSET($B$1,SMALL(IF($C$1:$C$10="Yes",ROW($C$1:
$C$10)),ROW(INDIRECT("1:"&$D$1)))-CELL("row",$E$1),0))&"'!"&CELL("address
",A1)),"<>"))

....confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

 

[Aladin Akyurek]

See also my contrib in:

http://www.excelforum.com//showthread.php?t=328999

which seems not to arrive at ms news group servers.