is there a shortcut for this formula.

[Doraemon]

I've this cell(M4) where it calculate from sheet 1 to sheet 72..
as I key untill sheet 47, excel prompt me formula too long.

If there a shortcut which I don't have to keyin all 72 sheet. Thanks.


Formula
=SUM(IF(Sheet1!M4=4,1,0)+IF(Sheet2!M4=4,1,0)+IF(Sheet3!M4=4,1,0) +IF(Sheet4!M4=4,1,0)+IF(Sheet5!M4=4,1,0)+IF(Sheet6!M4=4,1,0) +IF(Sheet7!M4=4,1,0)+IF(Sheet8!M4=4,1,0) +IF(Sheet9!M4=4,1,0)+IF(Sheet10!M4=4,1,0)+IF(Sheet11!M4=4,1,0) +IF(Sheet12!M4=4,1,0) +IF(Sheet13!M4=4,1,0)+IF(Sheet14!M4=4,1,0)+IF(Sheet15!M4=4,1,0)+IF(Sheet16!M4=4,1,0) +IF(Sheet17!M4=4,1,0)+IF(Sheet18!M4=4,1,0)+IF(Sheet19!M4=4,1,0)+IF(Sheet20!M4=4,1,0) +IF(Sheet21!M4=4,1,0) +IF(Sheet22!M4=4,1,0)+IF(Sheet23!M4=4,1,0)+IF(Sheet24!M4=4,1,0)+IF(Sheet25!M4=4,1,0) +IF(Sheet26!M4=4,1,0) +IF(Sheet27!M4=4,1,0) +IF(Sheet28!M4=4,1,0)+IF(Sheet29!M4=4,1,0)+IF(Sheet30!M4=4,1,0) +IF(Sheet31!M4=4,1,0) +IF(Sheet32!M4=4,1,0)+IF(Sheet33!M4=4,1,0)+IF(Sheet34!M4=4,1,0)+IF(Sheet35!M4=4,1,0) +IF(Sheet36!M4=4,1,0)+IF(Sheet37!M4=4,1,0)+IF(Sheet38!M4=4,1,0)+IF(Sheet39!M4=4,1,0) +IF(Sheet40!M4=4,1,0) +IF(Sheet41!M4=4,1,0)+IF(Sheet42!M4=4,1,0)+IF(Sheet43!M4=4,1,0)+IF(Sheet44!M4=4,1,0) +IF(Sheet45!M4=4,1,0) +IF(Sheet46!M4=4,1,0) +IF(Sheet47!M4=4,1,0) )

[daddylonglegs]

Some functions will support a "3D" reference, i.e. a reference that refers to multiple worksheets, unfortunately COUNTIF isn't one of these

What might you have in M4 if not a 4? If, for instance your M4s will either be 4 or blank you could use

=COUNT(Sheet1:Sheet72!M4)

which will give you a count of all M4s containing a number. Obviously, though, this will be no good for differentiating between 4 and 3 for instance

If you install morefunc addin you could use the COUNTIF.3D function and then employ:

=COUNTIF.3D(Sheet1:Sheet72!M4,4)

confirmed with CTRL+SHIFT+ENTER

[davesexcel]

Some functions will support a "3D" reference, i.e. a reference that refers to multiple worksheets, unfortunately COUNTIF isn't one of these

What might you have in M4 if not a 4? If, for instance your M4s will either be 4 or blank you could use

=COUNT(Sheet1:Sheet72!M4)

which will give you a count of all M4s containing a number. Obviously, though, this will be no good for differentiating between 4 and 3 for instance

If you install morefunc addin you could use the COUNTIF.3D function and then employ:

=COUNTIF.3D(Sheet1:Sheet72!M4,4)

confirmed with CTRL+SHIFT+ENTER

Thanks daddy,
I was trying to do
if(sum(sheet to sheet m4=this ) then such and such),
but could not get it to work.....
could there be a way of perfecting that formula???

[Doraemon]

Hi daddylonglegs and davesexcel,

Thanks. Got it sorted.