1. ### Welcome to Chandoo.org Forums. Short message for you

Hi Guest,

Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

Yours,
Chandoo
2. Hi All

Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

Post Spam and you Will Be Deleted as a User

Hui...

3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

# Sum of individual values in a cell

Discussion in 'Ask an Excel Question' started by bizlife, Jan 12, 2018.

1. ### bizlifeNew Member

Messages:
15
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.

Pankaj

Messages:
16,002
Hi ,

Narayan
3. ### HuiExcel NinjaStaff Member

Messages:
10,932
Pankaj

Try this UDF

Code (vb):
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)
Thomas Kuriakose likes this.
4. ### GraH - GuidoActive Member

Messages:
117
Without VBA, =CONCATENATE("=",A3,"+",A5,"+",A10) could do, but this is cumbersome.
5. ### bizlifeNew Member

Messages:
15
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.
6. ### bizlifeNew Member

Messages:
15
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.