Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 318
  • Last Modified:

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
Adult            John2                  $200
Child            John3                  $150
Adult            Jane4                  $300
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
0
rav_rav
Asked:
rav_rav
  • 5
  • 4
  • 3
1 Solution
 
Saurabh Singh TeotiaCommented:

Use this formula...

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

Saurabh...
0
 
Saurabh Singh TeotiaCommented:

Ravi,

Also please adjust the ranges as per your data.

Saurabh...
0
 
Rory ArchibaldCommented:
You would need VBA - is that OK?
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
rav_ravAuthor 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
 
rav_ravAuthor Commented:
VBA is fine, but I would prefer a formula if possible.
0
 
Rory ArchibaldCommented:
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
 
rav_ravAuthor Commented:
Hi rorya,

Your suggestion makes sense.

What would the function be in VBA?
0
 
Saurabh Singh TeotiaCommented:

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
 
Rory ArchibaldCommented:
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

Open in new window


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

Note: it would be best to use specific ranges rather than entire columns.
0
 
rav_ravAuthor 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
 
Rory ArchibaldCommented:
It removes the leading comma (or other separator) from the returned string.
0
 
rav_ravAuthor 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.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 5
  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now