Using If formula, & Formula, and Text in same cell

Hello,

Basing on what you described, you can always do =IF(F2>0,F2&" Dog","") & ...
If you can provide a sample file however, that would be so much easier for others to help you also.

This is messy, but see if it gets you where you want to be?

=IF(B2="","",SUM($B$2:B2)&" "&B$1&", ")&IF(C2="","",SUM($C$2:C2)&" "&C$1&", ")&IF(D2="","",SUM($D$2:D2)&" "&D$1&", ")&IF(E2="","",SUM($E$2:E2)&" "&E$1&", ")

Edit: Modified to deal with trailing comma's, thanks to Lemice's suggestion....
=IF(B2="","",SUM($B$2:B2)&" "&B$1&IF(COUNTA(C2:E2)=0,"",", "))&IF(C2="","",SUM($C$2:C2)&" "&C$1&IF(COUNTA(D2:E2)=0,"",", "))&IF(D2="","",SUM($D$2:D2)&" "&D$1&IF(E2="","",", "))&IF(E2="","",SUM($E$2:E2)&" "&E$1)

You can try this formula in A2

=IF(ISBLANK(B2),"",B2&" Dog")&IF(ISBLANK(C2),"",REPT(", ",--(COUNTA(B2:B2)>=1))&C2&" Cat")&IF(ISBLANK(D2),"",REPT(", ",--(COUNTA(B2:C2)>=1))&D2&" Chicken")&IF(ISBLANK(E2),"",REPT(", ",--(COUNTA(B2:D2)>=1))&E2&" Pig")

And here is your sample the formula.

I'm sorry if it isn't elegant at one bit, but at least it does the job ...

The hardest part was adding the comma. If you don't need it, it will be much easier.

If you're open to using a VBA function...

Copy and paste the code below into a general module.

Function aconcat(a As Variant, Optional sep As String = "") As String


'VBA code by Harlan Grove
   
    Dim y As Variant
 
    If TypeOf a Is Range Then
        For Each y In a.Cells
            aconcat = aconcat & y.Value & sep
        Next y
    ElseIf IsArray(a) Then
        For Each y In a
            aconcat = aconcat & y & sep
        Next y
    Else
        aconcat = aconcat & a & sep
    End If
 
    aconcat = Left(aconcat, Len(aconcat) - Len(sep))


End Function

Then, enter this array formula** in cell A1 and copy down as needed:

=SUBSTITUTE(SUBSTITUTE(TRIM(aconcat(IF(B2:E2<>"",
B2:E2&"^"&B$1:E$1,"")&" "))," ",", "),"^"," ")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

It would be better to do all the processing within the VBA function if you can get someone to write a function that will do that.

You're welcome. We appreciate the feedback!