Formula that sums up text from different cells (return multiple column headers in one cell

Dear All,

I have a list - attached an easy example.
Basically I need a formula to enter in B2, which will summarise for each item in which states it is being sold (if >0 display state abbreviation).
is it possible to do this with a formula - without macros etc?
Kind regards,
Sandra

example.xlsx[table="width: 500"]

States AR AZ FL
Item 1. AR, AZ 1 5 0
Item 2 0 0 0
Item 3 AZ, FL 0 2 3

given your example

g2 =SUM(C2:F2) and drag down.



I would solve this using pivot table

you add in your profile you use excel 2003 (xls), but you post an xlsx file (2007 or further).

With version do you use?

I used power pivot to rearange the data (excel 2010 add in / excel 2016 standard).

After that a pivot table.

See the attached file.

How many states are included?

I suspect that you'll need a UDF to do this neatly.

Please Login or Register  to view this content.

How to install your new code
Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Press Alt+F11 to open the Visual Basic Editor
Choose Insert > Module
Edit > Paste the macro into the module that appeared
Close the VBEditor
Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)
[B][I]


Then use the array-entered UDF:
=ConcatAll(IF($C2:$F2>0,$C$1:$F$1,""),", ")


...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.