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" wrote in message
...
If your sheets are in fact not named Sheet1, Sheet2, etc., try the
following instead...

=SUM(SUMIF(INDIRECT("'"&T(OFFSET(B1,SMALL(IF(C1:C1 0="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!

In article ,
"Steve W" wrote:

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