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. bizlife

    bizlife New 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.

    Thanks in advance.
    Pankaj
  2. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    16,465
    Hi ,

    Please upload a workbook which has enough data in it.

    Narayan
  3. Hui

    Hui Excel Ninja Staff Member

    Messages:
    11,266
    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 - Guido

    GraH - Guido Active Member

    Messages:
    528
    Without VBA, =CONCATENATE("=",A3,"+",A5,"+",A10) could do, but this is cumbersome.
  5. bizlife

    bizlife New 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. bizlife

    bizlife New 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. :)

Share This Page