excel column add specific

[qmann]

I want to take the items in a column and sum up the values only that are summed to begin with.
so if in the column row 3, 5, 8 etc have an autosum value i want to be able to pull out those autosum values and sum them up. The problem is i have over 7500 rows and to actually go in and select each cell individually will take an enormous amount of time ANybody have any ideas?

 

[RamziSaab]

hey this will do what u want...it assumes:

1. the data is in column 1 (mycols = 1, change this to what column your data is in)
2. the sheet is named Sheet1 (change thisworkbook.worksheets("sheet1&quot to the name of ur sheet
3.the anwer will be put in cell B1, if you need more help tell me

***********************************************************
Sub SumtheSums()

With ThisWorkbook.Worksheets("Sheet1&quot
i = .UsedRange.Rows.Count
mycols = 1

For myrows = 1 To i
If Left(.Cells(myrows, mycols).Formula, 4) = "=SUM" Then
strsum = strsum & "+" & .Cells(myrows, mycols).Address
End If
Next

.Cells(1, 2).Formula = "=" & Mid(strsum, 2)
End With
End Sub
**********************************************************

by the way this works by looking up the word sum in the formula so if u added them up munually it will be way more complicated

 

[Zymurgy35]

A way of doing this wihout code is to use the SUBTOTAL function. First you need to replace all the autoSUM formulas . To do this, use the find and replace option. Find "SUM(" and replace it with "SUBTOTAL(9,". Finally, add a SUBTOTAL formula below the end row as follows e.g. "=SUBTOTAL(9,A1:A7500)" this will only sum all the other SUBTOTALS.
Hope this helps.