# Creating a string with all cells that meet criteria for SUMIF formula

Hi Experts,

I've got a SUMIF formula I'm using to search a table and sum any line items which have a certain value in Column A.

Example:

ColA: Type      ColB: Name            ColC: Cost
Child            John1                  \$100
Child            John3                  \$150
Child            Jane5                  \$75
Child            Jane6                  \$50

So, SUMIF (A:A, "Child", C:C)= \$375

What command / formula can I use now to create a list of all the names associated with that summation in a cell as a string?  In the above example, I want to know that the cost of "\$375" is associate with "John1, John3, Jane5, Jane6, "

Thanks,
Ravi
###### Who is Participating?

Commented:
Like so:
``````Public Function ConcatIf(rngCrit As Range, crit, rngData As Range, Optional strSeparator As String = ",") As String
Dim lngindex As Long

For lngindex = 1 To rngCrit.Cells.Count
If Len(rngData(lngindex).value) > 0 And rngCrit(lngindex).value = crit Then ConcatIf = ConcatIf & strSeparator & rngData(lngindex).value
Next lngindex
If Len(ConcatIf) > 0 Then ConcatIf = Mid\$(ConcatIf, Len(strSeparator) + 1)
End Function
``````

Used:
=CONCATIF (A:A, "Child", B:B)

Note: it would be best to use specific ranges rather than entire columns.
0

Commented:

Use this formula...

=Sumproduct((A1:A100="Child")*(b1:b100="John1")*(C1:C100))

Saurabh...
0

Commented:

Ravi,

Saurabh...
0

Commented:
You would need VBA - is that OK?
0

Author Commented:
Hi Saurabh,

Thanks for the quick response, but could you explain your formula?  I tried it using the above sample data and I'm not getting a string value.

0

Author Commented:
VBA is fine, but I would prefer a formula if possible.
0

Commented:
You can use a function created in VBA so it works the same as a formula, but in order to use a native formula you would need a whole bunch of helper cells since there is no array concatenation function.
0

Author Commented:
Hi rorya,

Your suggestion makes sense.

What would the function be in VBA?
0

Commented:

Ahh sorry i thought you are looking for option how to sum values, In other way if you want to find out no. of parameters which matches the creteria you will either need to use a macro or you can create a pivot of it which automatically provide you details about what you are looking for...
0

Author Commented:
Hi rorya,

Quick question:  what's the last line "If Len(ConcatIf) > 0 Then ConcatIf = Mid\$(ConcatIf, Len(strSeparator) + 1)" used for?

Thanks
0

Commented:
It removes the leading comma (or other separator) from the returned string.
0

Author Commented:
Thanks rorya
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.