Sum all worksheets to a Single Worksheet

[dragonmouse]

I have multiple worksheets. In cell "M3" they all have the following formula adding up columns within it:

=IF(R3,R3+S3+T3," ")

On the Summary page I have this formula trying to add up all the worksheets but it's returning a VALUE. I want it to return a sum for all the worksheets in that cell (some weeks may NOT have a number). If none of the cells have a value then it should just return a blank cell. What I think should be easy just isn't working

=IF(OR('Week 42'!M3>0,'Week 44'!M3>0,'Week 46'!M3>0,'Week 48'!M3>0,'Week 50'!M3>0,'Week 52'!M3>0),('Week 42'!M3+'Week 44'!M3+'Week 46'!M3+'Week 48'!M3+'Week 50'!M3+'Week 52'!M3)," ")

 

[dragonmouse]

Slight change:

Each of my 6 worksheets has a formula:
=IF(OR(R3>0,S3>0,T3>0),(R3+S3+T3), " ")

This formula, IF any of three cells R3, S3, T3 have numbers in them it will add them or it will return a BLANK (it's too late to add to my first POST above)

There are 6 Sheets. The SAME cell on each sheet has this formula. I want to add ALL cells containing a number and consolidate it on a single summary sheet. IF there is NO data then I want it to return a blank.

The formula I tried isn't working. NOTE some sheets may not have data.

 

[Snakehips]

Assuming your Summary sheet is either the extreme left sheet tab or the extreme right sheet tab and your other tabs are, left to right.... Sheet2 to Sheet7 or whatever their names then try

=IF(ISERROR(1/SUM(Sheet2:Sheet7!M3)),"",SUM(Sheet2:Sheet7!M3))

Hope that helps.

 

[Eric W]

A few additional notes to Snakehips' post. You can create a 3-D SUM formula like he showed, but the sheets must all be consecutive. It looks like you're trying to add up Week 42, Week 44, etc. If you have a Week 43 in there, your totals will be off, unless you want to rearrange the sheets. Also, check the example in O3 below to see how to code it when the sheet names have spaces in them.

Book1
	O	P	Q
3	7.5		Week 42
4	7.5		Week 44
5	7.5		Week 52
Week 42

Cell Formulas
Range	Formula
O3	=SUM('Week 42:Week 52'!M3)
O4	=SUMPRODUCT(SUMIF(INDIRECT("'"&$Q$3:$Q$5&"'!M3"),">0"))
O5	=IFERROR(1/(1/SUMPRODUCT(SUMIF(INDIRECT("'"&$Q$3:$Q$5&"'!M3"),">0"))),"")

Finally, another option is to list the sheet names you want to include, then use a SUMPRODUCT formula to get the sum from those sheets (formula in O4). This version allows you to use the sheets in any order or position. Finally, another way to get a 0 to appear as an empty cell is shown in O5.

Hope this helps!

 

[dragonmouse]

Thank you for all your replies.
The following is working perfectly:

=IF(SUM('Week 42:Week 52'!S3)>0,SUM('Week 42:Week 52'!S3)," ")