# VBA Sum all worsksheets in column T

#### Weeble

##### Board Regular
Heya !

I have a pretty big excel file with aprox 60 worksheets. In every sheet column T has aprox 1000-4000 rows of data.
How would be the easiest way to summarise this data into a "summary-sheet" ?
There is alot of blank spaces in column T aswell if that might be an issue?

### Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

#### BarryL

##### Well-known Member
what are the worksheet names? are they a sequence or completely random?

#### Weeble

##### Board Regular
They are quite random. It's 52 weeks, with name of months and such.

#### BarryL

##### Well-known Member
so you'd like a summary sheet with each sheet name and a value for column T beside it?

#### Weeble

##### Board Regular
The only thing I need is the total sum of all sheets in one cell. No need for sheet name.

#### BarryL

##### Well-known Member
Make a list of the worksheet names in a new sheet lets say from A1:A60 then put the below in A1 and drag down.

=SUM(INDIRECT("'"&A2&"'!T:T"))

#### BarryL

##### Well-known Member
The only thing I need is the total sum of all sheets in one cell. No need for sheet name.

right youll need a named range with all your sheet names then and the use sum with sumproduct

#### BarryL

##### Well-known Member
create a list f all of your sheets. Then add them to a named range , Lets call it SHTS. then use the below formula

=SUMPRODUCT(SUM(INDIRECT("'"&SHTS&"'!T:T")))

#### Weeble

##### Board Regular
I think this will work fine Thx for your help BarryL!

#### Rick Rothstein

##### MrExcel MVP
The only thing I need is the total sum of all sheets in one cell. No need for sheet name.
Here is a UDF (user defined function) that should give you that total...
Code:
``````Function TotalColT() As Double
Dim WS As Worksheet
For Each WS In ThisWorkbook.Worksheets
TotalColT = TotalColT + Application.Sum(WS.Range("T:T"))
Next
End Function``````

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use TotalColT just like it was a built-in Excel function. For example,

=TotalColT()

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Last edited:

Replies
3
Views
218
Replies
3
Views
269
Replies
25
Views
470
Replies
3
Views
218
Replies
4
Views
500

1,191,054
Messages
5,984,378
Members
439,883
Latest member
onions44

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back