I would use a macro. And JE McGimpsey has a macro that can be modified:
http://www.mcgimpsey.com/excel/udfs/multicat.html
If you want to try:
Option Explicit
Public Function MultiCatIf(ByRef CritRng As Range, _
ByVal myOperator As String, _
ByVal myVal As Variant, _
ByRef ConCatRng As Range, _
ByVal sDelim As String, _
ByVal AllowDuplicates As Boolean) _
As Variant
Dim myStr As String
Dim iCtr As Long
Dim CritVal As Variant 'number or string
Dim ConCatVal As String
Dim myExpression As String
Dim OkToInclude As Variant 'should be boolean, but could be an error
Dim KeepThisVal As Boolean
If CritRng.Columns.Count <> 1 _
Or ConCatRng.Columns.Count <> 1 Then
MultiCatIf = CVErr(xlErrRef)
Exit Function
End If
If CritRng.Rows.Count <> ConCatRng.Rows.Count Then
MultiCatIf = CVErr(xlErrRef)
Exit Function
End If
If Application.IsNumber(myVal) Then
'keep it a number, do nothing
Else
'surround it by double quotes
myVal = Chr(34) & myVal & Chr(34)
End If
myStr = ""
For iCtr = 1 To ConCatRng.Cells.Count
CritVal = CritRng.Cells(1).Offset(iCtr - 1, 0).Value2
If Application.IsNumber(CritVal) Then
'leave it be
Else
CritVal = Chr(34) & CritVal & Chr(34)
End If
myExpression = CritVal & myOperator & myVal
'using the same non-case sensitive compare that excel uses
OkToInclude = Application.Evaluate(myExpression)
If IsError(OkToInclude) Then
'skip it
Else
If OkToInclude = True Then
ConCatVal = ConCatRng.Cells(1).Offset(iCtr - 1, 0).Text
KeepThisVal = True
If AllowDuplicates = False Then
'look for duplicates
If InStr(1, sDelim & myStr & sDelim, _
sDelim & ConCatVal & sDelim, vbTextCompare) > 0 Then
KeepThisVal = False
End If
End If
If KeepThisVal = True Then
'add it to the string
myStr = myStr & sDelim & ConCatVal
End If
End If
End If
Next iCtr
If myStr = "" Then
'do nothing
Else
'get rid of that leading delimiter
myStr = Mid(myStr, Len(sDelim) + 1)
End If
MultiCatIf = myStr
End Function
If you're new to macros:
Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html
David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm
(General, Regular and Standard modules all describe the same thing.)
Short course:
Open your workbook.
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)
right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side
Paste the code in there.
Now go back to excel.
Into a test cell and type:
=multicatif(A1:A4,">",750000,C1:C4,", ",FALSE)
This passes 6 parms:
Public Function MultiCatIf(ByRef CritRng As Range, _
ByVal myOperator As String, _
ByVal myVal As Variant, _
ByRef ConCatRng As Range, _
ByVal sDelim As String, _
ByVal AllowDuplicates As Boolean)
1. Criteria range
2. Comparison operator
3. Value to be compared
4. Concatenation range
5. Delimiter
6. Allow duplicates
(I thought it would be nice to use the same code to allow/prohibit duplicates.)
You may want to try:
=multicatif(A1:A4,">",750000,C1:C4,CHAR(10),FALSE)
And format the cell to wrap text.
=char(10) is the same as the alt-enter.
====
The function does expect that the ranges have the same dimensions--1 column by
XX rows.
If you're industrious, you may want to modify it for x rows by y columns and
loop through each dimension in the same pattern.
