Sum all worksheets to a Single Worksheet

dragonmouse

Board Regular
Joined
May 14, 2008
Messages
87
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)," ")
 

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

dragonmouse

Board Regular
Joined
May 14, 2008
Messages
87
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

Well-known Member
Joined
May 17, 2009
Messages
5,057
Office Version
2013
Platform
Windows
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.
 
Last edited:

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
9,922
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.<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>O</th><th>P</th><th>Q</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;">7.5</td><td style="text-align: right;;"></td><td style=";">Week 42</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;">7.5</td><td style="text-align: right;;"></td><td style=";">Week 44</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;">7.5</td><td style="text-align: right;;"></td><td style=";">Week 52</td></tr></tbody></table><p style="width:5.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Week 42</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">O3</th><td style="text-align:left">=SUM(<font color="Blue">'Week 42:Week 52'!M3</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">O4</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">SUMIF(<font color="Red">INDIRECT(<font color="Green">"'"&$Q$3:$Q$5&"'!M3"</font>),">0"</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">O5</th><td style="text-align:left">=IFERROR(<font color="Blue">1/(<font color="Red">1/SUMPRODUCT(<font color="Green">SUMIF(<font color="Purple">INDIRECT(<font color="Teal">"'"&$Q$3:$Q$5&"'!M3"</font>),">0"</font>)</font>)</font>),""</font>)</td></tr></tbody></table></td></tr></table><br />

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

Board Regular
Joined
May 14, 2008
Messages
87
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)," ")
 

Watch MrExcel Video

Forum statistics

Threads
1,102,778
Messages
5,488,823
Members
407,658
Latest member
Arias610

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top