Excel 2007 : Excel IF formula with multiple returns

Hi...need some help! I have a number of columns with Yes or No data...I need to create a formula that will return text for EACH of the Yes values into ONE field.
Example...
Let's say the columns are labled "Toys", "Music", "Games", "Food", "Books", "Plants"...
One particular row under these headings might say "Yes", "Yes", "No", "No", "Yes".
How would you write a formula that will return the following text: "Toys - Music - Plants" ?

These are concatenation formulas, usually.

=A1 & " - " & B1 & " - " & C1

But this often isn't all that is necessary to get your end results to look correct.

Click GO ADVANCED and use the paperclip icon to post up a copy of your workbook, mockup the desired results you want to see right on the sheet itself.

This ability is not inherent to Excel. But there are people who have created special User Defined Functions to do this easily for you. My favorite is called CONCATIF() and it functions a lot like a SUMIF() formula.

First, here's the code for the new function.

Please Login or Register  to view this content.

It's used just like a SUMIF() formula, but it has two extra parameters.

=ConCatIf(D2:AA2,"yes",$D$1:$AA$1," - ",TRUE)

First parameter: Range to evaluate
Second param: comparison string.
Third param: Range to return values from
Fourth param: The delimiting string for the concatenation
Fifth param: (optional) TRUE means eliminate duplicated values, FALSE (or omitted) means string all values

This should give you enough info to apply this UDF to your sheet and use it place of that formula of your own.

How to install the User Defined Function:

1. Open up your workbook
2. Get into VB Editor (Press Alt+F11)
3. Insert a new module (Insert > Module)
4. Copy and Paste in your code (given above)
5. Get out of VBA (Press Alt+Q)
6. Save as a macro-enabled workbook

The function is installed and ready to use.

Jerry -- this is wonderful!! I applied it to my example and it worked perfectly, but in my actual worksheet the columns in question are AD - BA (instead of D - AA), so I did everything the same but adjusted the formula to this: =ConCatIf(AD2:BA2,"yes",$AD$1:$BA$1," - ",TRUE) But it doesn't seem to be working...all I get is #NAME? Where am I going wrong now?

Wait...don't answer...I think I've found my error!!

YESSSSSSSSSSSSSSSSSSS!!! It's working!!

Thanks, Jerry.... excellent assistance!! Many thanks! <3

Glad to help, though the credit for this one really goes to Mike Rickson.

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

Jerry, whats the process for saving this as a function/Addin that can be used in any workbook?

I don't do addins, its a philosophical difference only. I can embed the function into the workbooks needed and they travel with the workbook out to anyone I send them to and they keep functioning properly. If I went the ADDIN route I'd have to be instructing others on how to add in the addin on all their computers. Nah.

Meanwhile, turning a UDF into an addin is simple. Google that question and you'll get 100s of answers.

Also, Scottlady2, you know the rules about hijacking people's threads with your own questions, and this is about as unrelated as you can get while still being slightly related.