SUM the values from all the SUM Formula in a sheet

[srinidhi]

My sheet has 5000 rows of data & in between there are sum formulas

I want to sum only the values in the sum formula


Ex: Numbers A1: A10, A11 =sum(A1:A10)

Ex: Numbers A15: A30, A32 =sum(A15: A30)


I would like to sum only the values in A11 & A32 & not the entire range from A1:A30


IS there any formula to sum only the values from formulas

 

[kaushik03]

Hi srinidhi,


As far as I know, there is no way to know if a cell contains a formula using Excel formulas only (Ninjas / experts...plz correct me if I am wrong)!!


It can be done through VBA; precisely, using an UDF as follows:

[pre]

Public Function HasFormula(rng As Range) As Variant()
Dim MyArry() As Variant
Dim m As Integer, p As Integer
Dim Store() As Variant

MyArry = rng.Value
ReDim Store(LBound(MyArry) To UBound(MyArry), _
LBound(MyArry, 2) To UBound(MyArry, 2))
For m = LBound(MyArry) To UBound(MyArry)
For p = LBound(MyArry, 2) To UBound(MyArry, 2)
If (Left(rng(m, p).Formula, 1) = "=") Then
Store(m, p) = True
Else
Store(m, p) = False
End If
Next p
Next m
HasFormula = Store()
End Function

[/pre]

Insert the above code in a module. Then go back to your sheet where you have the data and use the below array formula to get the sum of the cells which has the sum formula only:


=SUM(A1:A33)-SUM(IF(NOT(HasFormula(A1:A33)),A1:A33,0)) (CTRL+SHIFT+ENTER)


[A1:A33 is your data range]


Plz also downloadthe file from here:


http://speedy.sh/r2FGu/IFForm.xlsm


Kaushik

 

[srinidhi]

Hey kaushik, thanks a lot, Macros will not be of much help to me as the workbook changes every time. I can convert the same to excel addin & then add it to excel.


Thanks a ton, you have saved me quite a lot of time.

 

[kaushik03]

Glad to help you friend...


Kaushik