Excel Formula If/Then Maybe?

[squeakums]

Trying to write up something like this:

=IF(C9:C18>=1, B9+C9, "")

Basically its looking in cells c9:c18, if any has a value 1 or greater than pull name from b9:b18 and then value from c9:c18, trying to combine it. The end result should look like this:

Livingroom (3), Bedroom (1),

Placing them in this other field.

 

[Eric W]

If you have Excel 365 with the TEXTJOIN function, you can do this:

=TEXTJOIN(", ",TRUE,IF(C9:C18>0,B9:B18&" ("&C9:C18&")",""))

confirming the formula with Control+Shift+Enter, not just enter.

If you don't have TEXTJOIN, we can build a formula using 10 helper cells, or we can turn to VBA to write a custom function.

 

[squeakums]

Getting an error, even with the ctrl, shift and enter with the brackets added. I only want it to pull each cell if it has a value over 0, otherwise, don't pull that particular cell.

 

[Eric W]

Do you have the TEXTJOIN function? If not, you will get the #NAME ? error. You can use the helper column method like this:

	B	C	D	E	F
Livingroom (3), Bathroom (2), Bedroom (1)
Livingroom (3), Bathroom (2), Bedroom (1)
Livingroom	, Livingroom (3)
Balcony	, Livingroom (3)
Bathroom	, Livingroom (3), Bathroom (2)
Bedroom	, Livingroom (3), Bathroom (2), Bedroom (1)
Den	, Livingroom (3), Bathroom (2), Bedroom (1)
Theater	, Livingroom (3), Bathroom (2), Bedroom (1)
, Livingroom (3), Bathroom (2), Bedroom (1)
, Livingroom (3), Bathroom (2), Bedroom (1)
, Livingroom (3), Bathroom (2), Bedroom (1)
, Livingroom (3), Bathroom (2), Bedroom (1)

<tbody>
[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="align: right"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet7


[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D9[/TH]
[TD="align: left"]=IF(C9,D8&", "&B9&" ("&C9&")",D8)&""[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E7[/TH]
[TD="align: left"]=MID(D18,3,255)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E6[/TH]
[TD="align: left"]{=TEXTJOIN(", ",TRUE,IF(C9:C18>0,B9:B18&" ("&C9:C18&")",""))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



Put the D9 formula in (there should be nothing in D8), then copy down to D18. Then the E7 formula will give you want you want. You can put these cells in column Z, or hide them if you want. The E6 formula is to show a working version of TEXTJOIN, which generates the same thing.

 

[squeakums]

I do not have a textjoin formula in my list of formulas, so perhaps thats why? Is it one that I download or is it in a later version of excel?

 

[Eric W]

TEXTJOIN is currently only available in Excel 365, which I mentioned in post 2. I believe that it is also available in Excel 2019 which has just been released. You can't download TEXTJOIN for your current version, but if you upgrade to one of those versions it will be available. If you're not ready to upgrade, you have 2 choices. First, you can use the helper column method I demonstrated in post 4. Second, you can use a UDF (User-Defined Function) which I wrote which is a clone of TEXTJOIN. If you want to try that,

1) Open a copy of your workbook
2) Right click on the sheet tab on the bottom and select View Code
3) From the <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-top-style: initial; border-right-style: initial; border-bottom-style: dotted; border-left-style: initial; border-top-color: initial; border-right-color: initial; border-bottom-color: rgb(0, 0, 0); border-left-color: initial; border-image: initial; font-variant-numeric: normal; font-variant-east-asian: normal; cursor: help; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">VBA</acronym> menu, select Insert > Module
4) On the sheet that opens, paste this code:

Function TextJoinX(sep As String, ign As Boolean, ParamArray SubArr() As Variant) As String
Dim i As Long, y As Variant
' Clone of TextJoin for versions of Excel prior to 2016
' 2016, Eric W, adapted from AConcat by Harlan Grove

    TextJoinX = ""
    For i = LBound(SubArr) To UBound(SubArr)
        If TypeOf SubArr(i) Is Range Then
            For Each y In SubArr(i).Cells
                If y = "" And ign Then
                Else
                    TextJoinX = TextJoinX & sep & y.Value
                End If
            Next y
        ElseIf IsArray(SubArr(i)) Then
            For Each y In SubArr(i)
                If y = "" And ign Then
                Else
                    TextJoinX = TextJoinX & sep & y
                End If
            Next y
        Else
            If SubArr(i) = "" And ign Then
            Else
                TextJoinX = TextJoinX & sep & SubArr(i)
            End If
        End If
    Next i
    
    TextJoinX = Mid(TextJoinX, Len(sep) + 1)
        
End Function

5) Close the <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-top-style: initial; border-right-style: initial; border-bottom-style: dotted; border-left-style: initial; border-top-color: initial; border-right-color: initial; border-bottom-color: rgb(0, 0, 0); border-left-color: initial; border-image: initial; font-variant-numeric: normal; font-variant-east-asian: normal; cursor: help; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">VBA</acronym> editor with Alt-Q

Now you can use the exact formula from post 2. Just change TEXTJOIN to TEXTJOINX, and change the ranges to match your sheet, then confirm with Control+Shift+Enter.