This Excel tutorial explains how to use Excel 3D Sum to Sum the same Cells in different worksheets.
You may also want to read:
What is Excel 3D Sum
Excel 3D Sum to Sum the same Cells in different worksheets. You can achieve the same task without 3D Sum, but 3D Sum can simply your formula. The name “3D Sum” is not a Function name, you cannot use a Function like =3DSum() , it is just a special way to use SUM Function.
Before you know Excel 3D Sum
Assume that you have 4 worksheets: consol, Jan, Feb, Mar.
Your task is to type a formula in worksheet “consol” Cell A1, to sum Cell A1 of all other worksheets (from Jan to Mar).
Normally, you type the following formula
In the above example, we only sum up 3 months, you can imagine how difficult it would be if you have to sum up many worksheets.
Use Excel 3D Sum – to Sum the same Cells in different worksheets
To use 3D Sum, change the formula to
Now you have greatly simplified the formula, but note that those selected worksheets must be adjacent to each other, otherwise enter the second parameter.
For example, we have worksheet Jan, Feb, Mar, Apr, May, and we only need to sum Jan to Mar plus May
Use other 3D Functions
This “3D” technique is not confined to Sum Function, it can be used for other aggregate Function as well. For example, Average, Min, Max, Count, etc.
Use VBA to sum all worksheets
3D Sum fails to select specific worksheets to Sum, it can only select a Range of continuous worksheets, using VBA to do calculation (not only sum) is more flexible because it allows Wildcards.
|*||Represents one or more characters (any character)||J* any text that starts with J
*J starts with any text but ends with J
*J* any text that has J in the middle
|?||Represents one character (any character)||J? 2 characters that start with J
?J 2 characters that end with J
?J? 3 characters with J in the middle
|~||Treat * or ? as the actual character but not wildcard. Used ~ followed by * or ?||J~** any text that starts with J*
~**J any text that starts with * and ends with J
~?*~* any text that starts with ? and ends with *
The below VBA codes sum up all Cell B30 of worksheet with name starting with “employee”.
Sub consolWS() Dim ws As Worksheet For Each ws In ActiveWorkbook.Worksheets If ws.Name Like "employee*" Then ttl = ttl + ws.Range("B30").Value End If Next ws Sheets("Summary").Range("A1") = ttl End Sub
Sort worksheet before 3D sum
If our worksheet is in order of Jan > Feb > Mar
Apply 3D sum from Jan:Mar will add all values up, but what if our order becomes Jan > Mar > Feb , but our formula remains from Jan:Mar? The 3D Sum formula will not sum the Feb data. Therefore it is very important to make sure the formula is in our expected order. Run the below Macro customSort() to sort the worksheet in a desired order. You may also refer to my previous post if you want to sort worksheet in descending / ascending order.
Sub customSort() For i = 1 To Worksheets.Count For j = i To Worksheets.Count If wCustomSort(Sheets(j).Name) < wCustomSort(Sheets(i).Name) Then Sheets(j).Move before:=Sheets(i) End If Next j Next i End Sub Public Function wCustomSort(Item) As Integer Dim arrayList As Variant arrayList = Array("Finance", "HR", "IT", "Legal") 'put your items here in desired order wCustomSort = 1000 'if the item is not defined in arrayList, assign a default order as 1000 For i = LBound(arrayList) To UBound(arrayList) If arrayList(i) = Item Then wCustomSort = i End If Next i End Function
To automate running this Macro when workbook is opened, insert the below code in Workbook_Open Event.
Private Sub Workbook_Open() Call customSort End Sub