CONCATENATE with multiple IFS

[mrnarley]

Hello,

The formula below works great for counting. I need to use the same formula to CONCATENATE all of the names in B:B. It seems as though I should just replace COUNTIFS with CONCATENATE and change Data!C:C to Data!B:B, but it doesn't work. I tried a search on the web using the title of this thread but came up with very few clues. Is it even possible to CONCATENATE with multiples IFS? Thanks!

=COUNTIFS(Data!C:C,IF(B2="All","*",B2),Data!D:D,IF(B3="All","<4",B3),Data!E:E,">="&W5,Data!E:E,"<="&X5)

 

[mrnarley]

Hi,

Just bumping this thread hoping to get some help. Thanks!

 

[venkat1926]

quote
=COUNTIFS(Data!C:C,IF(B2="All","*",B2),Data!D:D,IF(B3="All","<4",B3),Data!E:E,">="&W5,Data!E:E,"<="&X5)
unquote



thisis ok for count if
but when you concatenate what your are doing
you find ALL the values in the whole column C and add to that text the result of first if etc
and then all values of column D etc etc

will the folliwng formula (truncated formula ) help you?

=CONCATENATE(IF(B2=A11,"",B2),IF(B3="all","<4",B3))

 

[mrnarley]

Well, I tried to blend your version with my criteria like this:

=CONCATENATE(Data!B:B),IF(B2=A11,"",B2),IF(B3="all","<4",B3),Data!E:E,">="&W5,Data!E:E,"<="&X5)

I'm getting a popup window indicating the formula is no good. I'm trying to concatenate the names in B:B that fit the criteria range starting at the first IF command. I'm not sure why it doesn't work. I'm learning fast, but I'm just not that good yet. There are parts of the formula language that I don't yet fully understand. I'm proably missing something simple.

 

[venkat1926]

what do want to find. take your first if
assume B2=a11 then cell where you have the formula(only if condition) becomes blank
are ou searrching for the blank in B:B. no
suppose B2<>A11 then the cell of theformula is B2 value . you want of find out this cell values in B:B which is nothing but finding B2 itslef. Is there any other cells in B:B having the same value as B2 the logic is not clear.

I suggest post a small extrct of sheet preferably using html maker of the newsgroup and clarly enunciate the logic. You may get a solution or atleast the way to it.

 

[mrnarley]

Thanks. I'll try to be more clear.

The following formula works wonderfully well for counting the number of records that match my criteria.

=COUNTIFS(Data!A:A,IF(B1="All","*",B1),Data!C:C,IF(B2="All","*",B2),Data!D:D,IF(B3="All","<4",B3),Data!E:E,">="&W5,Data!E:E,"<="&X5)

I want to replace the COUNTIFS in the above string with CONCATENATE. I also want to replace Data!A:A with Data!B:B since B:B is the row that contains the names I'd like to CONCATENATE. No change to the criteria at all. The goal is to count the amount of records that match the criteria and also report all of the names associated with those records in one cell. I have tried to replace the code as explained above but it doesn't work. I don't understand why. I feel like the answer is staring me in the face and I can't see it. If it helps, the following is true:

B1 is a drop down box containing "All" and various names of regions.
B2 is a drop down box containing "All" and various names of project types.
B3 is a drop down box containing "All" and the numbers 1,2,3.
W5 contains a date.
X5 contains a date.

Is that enough information?

Thanks for all of your help.

 

[mrnarley]

I just read my post again and realized a part of my text is misleading. I do not wish to blend the count and names all in one cell. The COUNTIF reports the amount of records found, an it returns that value to a single cell on my worksheet. The CONCATENATE returns all of the names in a seperate cell on my worksheet. Sorry.

 

[mikerickson]

This UDF, ConcatinateIf, takes the same arguments as SUMIF and COUNTIF, in addition to the (optional) Delimiter and (optional) NoDuplicates arguments.

Function ConcatIf(ByVal compareRange As Range, ByVal xCriteria As Variant, Optional ByVal stringsRange As Range, _
                            Optional Delimiter As String, Optional NoDuplicates As Boolean) As String
    Dim i As Long, j As Long
    With compareRange.Parent
       Set compareRange = Application.Intersect(compareRange, Range(.UsedRange, .Range("a1")))
    End With
    If compareRange Is Nothing Then Exit Function
    If stringsRange Is Nothing Then Set stringsRange = compareRange
    Set stringsRange = compareRange.Offset(stringsRange.Row - compareRange.Row, _
                                                stringsRange.Column - compareRange.Column)
    
        For i = 1 To compareRange.Rows.Count
            For j = 1 To compareRange.Columns.Count
                If (Application.CountIf(compareRange.Cells(i, j), xCriteria) = 1) Then
                    If InStr(ConcatIf, Delimiter & CStr(stringsRange.Cells(i, j))) <> 0 Imp Not (NoDuplicates) Then
                        ConcatIf = ConcatIf & Delimiter & CStr(stringsRange.Cells(i, j))
                    End If
                End If
            Next j
        Next i
        ConcatIf = mid(ConcatIf, Len(Delimiter) + 1)
End Function

 

[mrnarley]

Wow! That's VBA, right? Thanks for taking the time. Although it interests me greatly, I have yet to get into coding. I'm getting the idea I may have use VBA to solve my problem. It's important that I get the answer so I'll do what I have to do. Before I head down that road, is there no way to tweak the string I already have for COUNTIF and make it work for CONCATENATE?

 

[mikerickson]

CONCATINATE can't be used with arrays or ranges.
My version of Excel doesn't support COUNTIFS, can you describe in words what the conditions are that determine which strings get concatinated?