Excel 3D Sum to Sum the same Cells in different worksheets

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

 

Outbound References

https://support.office.com/en-US/article/SUM-function-0761ed42-45aa-4267-a9fc-a88a058c1a57?ui=en-US&rs=en-US&ad=US