Sum of individual values in a cell

[bizlife]

Hi all,

I have a sheet where there is data in column A. I want to sum few cells and display the result in a cell showing individual values as well. For eg - cell A3 is 200, A5 is 120, A10 is 180. I want the sum in cell B1(say) to be shown as =200+120+180. I will be filtering the values in column A based on criterias in different columns, hence the cells are different everytime the criteria changes. I used subtotal but it only shows the total value and not the individual values being summed up. Please help me do this.

Thanks in advance.
Pankaj

 

[NARAYANK991]

Hi ,

Please upload a workbook which has enough data in it.

Narayan

 

[Hui]

Pankaj

Try this UDF

Function Show_Filtered_Sum(myRng As Range) As String

Dim SubTot As String
Dim c As Range

Application.Volatile
Application.EnableEvents = False

SubTot = "="
For Each c In myRng
    If c.EntireRow.Hidden = False Then
        SubTot = SubTot + CStr(c) + "+"
    End If
Next

Show_Filtered_Sum = Left(SubTot, Len(SubTot) - 1)

Application.EnableEvents = True

End Function

to use it either:

=Show_Filtered_Sum(Table1[Field Name])
or
=Show_Filtered_Sum(C1:C100)

 

[GraH - Guido]

Without VBA, =CONCATENATE("=",A3,"+",A5,"+",A10) could do, but this is cumbersome.

 

[bizlife]

Hi ,

Please upload a workbook which has enough data in it.

Narayan

Sorry for not uploading the workbook. I understand it becomes easier with an example workbook. Will upload one everytime I ask a query in future.
Thanks.

 

[bizlife]

Pankaj

Try this UDF

Function Show_Filtered_Sum(myRng As Range) As String

Dim SubTot As String
Dim c As Range

Application.Volatile
Application.EnableEvents = False

SubTot = "="
For Each c In myRng
    If c.EntireRow.Hidden = False Then
        SubTot = SubTot + CStr(c) + "+"
    End If
Next

Show_Filtered_Sum = Left(SubTot, Len(SubTot) - 1)

Application.EnableEvents = True

End Function

to use it either:

=Show_Filtered_Sum(Table1[Field Name])
or
=Show_Filtered_Sum(C1:C100)

Hi Hui,
That worked like magic. Thanks for the help.
I have just started learning VBA and the power of coding amazes me everyday. Please help me with tips to learn the language faster and in depth.

Thanks once again.