Sum all worksheets to a Single Worksheet

dragonmouse

Board Regular
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)," ")

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
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
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

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
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)," ")

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...