Sum the same the cell from multiple tab

[asdafgafawaaa]

Hi all,

I'm working on a workbook containing 150 sheets, 50 of which has "sales" in the sheet name.

Is there a function that sum the same cell (like D3) of those 50 sheets? They are not in sequence, the 150 sheets are mixed up.

Can we use a formula like - =sum( "'Sales" & "*!'" &"D3" )?

Thank you so much!

 

[offthelip]

ths UDF will do it for you, note I have made the UDF volatile ( just to show you how to, but you may not need , if you don't comment it out because it will force the UDF to be calculated every recalculation which could mke your workbook slower)
To find out more about this look at this link:
https://www.mrexcel.com/forum/excel-questions/271165-udf-volatile-vs-not.html

Function sumsheets()
Application.Volatile ' use this if you need it


Dim ws As Worksheet
sumsheets = 0
For Each ws In Worksheets
 If Left(ws.Name, 5) = "Sales" Then
   sumsheets = sumsheets + ws.Range("D3")
 End If
Next


End Function

to use it just put
=sumsheets()
in a cell.
You could change it to specify a range if you want to make it more flexible

 

[Eric W]

You can do a 3-D function, like:

=SUM('Sales Jan 2019:Sales Dec 2019'!D3)

where all the sheets you want to sum have to be together, and you use the first and last names of the sheets in the formula. Some people like to actually create blank sheets named "Start" and "End" and put them around the sheets you want to sum.