Ask Your Question
0

Function for strings (ConcatIf) like SumIf

asked 2017-08-07 14:13:07 +0100

this post is marked as community wiki

This post is a wiki. Anyone with karma >75 is welcome to improve it.

Is there function for string like SumIf (concatenate instead sum)?

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted
1

answered 2017-08-07 15:30:39 +0100

JohnSUN gravatar image

Yes, for this task you can use combination of functions TEXTJOIN() and IF() For example, if you have in range A2:A100 some text strings and in range B2:B100 condition for concatenate (or skip any cells) then use array formula like as

{=TEXTJOIN(", ";1;IF(B2:B100;A2:A100;""))}
edit flag offensive delete link more
0

answered 2017-08-07 15:29:36 +0100

passat gravatar image

updated 2017-08-07 15:32:16 +0100

User function:

Function ConcatIf ( compareRange As Variant, xCriteria As String, stringsRange As Variant, Optional Delimiter As String)

Dim nConcatif As String

nConcatif = ""

For i = 1 To ubound ( compareRange, 1 )

  If  compareRange (  i, 1 ) = xCriteria Then
      nConcatIf = nConcatIf & Delimiter & stringsRange ( i, 1 )           
  End If

Next i

nConcatIf=Mid(nConcatIf, Len(Delimiter) + 1)

ConcatIf = nConcatIf

End Function

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2017-08-07 14:13:07 +0100

Seen: 228 times

Last updated: Aug 07 '17