• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 317
  • 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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
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

Featured Post

Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

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