VBA Sum all worsksheets in column T

[Weeble]

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?

 

[Weeble]

You're right.

I use the usual solution of adding one worksheet before and one after the group of worksheets in the formula. I name them Start and End and then use

=SUM(Start:End!T:T)

This way I can insert, delete or move worksheets as long and they remain between Start and End and the formula will always work.

I like this method alot! Can you use it with a sum.if function? I get a #Value error when I try it.
=SUM.IF('1:57'!S:S;"M3";'1:57'!P:P) , using it without :57 it works. But when I try to add in start to end it fails. any thoughts?

 

[pgc01]

Can you use it with a sum.if function?

No, 3D ranges do not work with conditional formulas.

You'll have to either use the formula solution with the list of worksheets and Indirect() or a udf.

 

[Weeble]

No, 3D ranges do not work with conditional formulas.

You'll have to either use the formula solution with the list of worksheets and Indirect() or a udf.

ok, do you know what I would need to add to get the results? =SUM.IF(S:S;"M3";P:P) Application.Sum(WS.Range("T:T")) ?

 

[pgc01]

ok, do you know what I would need to add to get the results? =SUM.IF(S:S;"M3";P:P) Application.Sum(WS.Range("T:T")) ?

In Rick's code use Application.SumIf(WS.Range("S:S"),"M3",WS.Range("P:P"))