This Excel tutorial explains how to use Excel 3D Sum to Sum the same Cells in different worksheets.

You may also want to read:

Excel VBA loop through worksheets in the same workbook

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

=Jan!A1+Feb!A1+Mar!A1

OR

=SUM(Jan!A1,Feb!A1,Mar!A1)

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

=SUM(Jan:Mar!A1)

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

=SUM(Jan:Mar!A1,May!A1)

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

Wildcard | Meaning | Example |

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

Hi, Wyman,

Actually we can use wildcards in referencing other sheets directly, without VBA.

http://wmfexcel.com/2015/07/11/sumc3-is-it-a-valid-formula-no-it-is-magical-indeed/

Cheers,

Thanks for your brilliant sharing ^^