CONCATENATE with multiple IFS

mrnarley

New Member
Joined
Jan 8, 2008
Messages
36
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)
 

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

venkat1926

Well-known Member
Joined
Aug 21, 2005
Messages
4,824
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

New Member
Joined
Jan 8, 2008
Messages
36
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

Well-known Member
Joined
Aug 21, 2005
Messages
4,824

ADVERTISEMENT

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

New Member
Joined
Jan 8, 2008
Messages
36
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

New Member
Joined
Jan 8, 2008
Messages
36

ADVERTISEMENT

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

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,914
This UDF, ConcatinateIf, takes the same arguments as SUMIF and COUNTIF, in addition to the (optional) Delimiter and (optional) NoDuplicates arguments.
Code:
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

New Member
Joined
Jan 8, 2008
Messages
36
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

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,914
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,865
Messages
5,598,539
Members
414,246
Latest member
allyciv

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top