if cell has any value, then return text in different cell

[liloo]

Hi Everyone,

I created a simple table to illustrate the data I have in my original workbook. In my workbook, this table is pulled from another sheet using VLOOKUP. The values are quantity purchased by each company. "-" indicates the company did not purchase anything in said year for said product. "0" indicates the company did purchase said product, in a particular quarter of the year.

I would like to show in a new column the tally for each company. I want to write a formula that would spit out "2019Q4_A, 2019Q2_B, 2020Q2_B, 2020Q4_C" for ABC Inc. I don't know where to start. I've been looking up different functions and imagine this may be a combination of VLOOKUP, ISNUMBER and COUNTIF???

Any guidance is much appreciated!

Book1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y
1		2019Q1 Product A	2019Q2 Product A	2019Q3 Product A	2019Q4 Product A	2019Q1 Product B	2019Q2 Product B	2019Q3 Product B	2019Q4 Product B	2019Q1 Product C	2019Q2 Product C	2019Q3 Product C	2019Q4 Product C	2020Q1 Product A	2020Q2 Product A	2020Q3 Product A	2020Q4 Product A	2020Q1 Product B	2020Q2 Product B	2020Q3 Product B	2020Q4 Product B	2020Q1 Product C	2020Q2 Product C	2020Q3 Product C	2020Q4 Product C
2	ABC Inc	0	0	0	4	0	6	0	0	-	-	-	-	-	-	-	-	0	10	0	0	0	0	0	20
3	DEF Corp	-	-	-	-	1	0	0	2	-	-	-	-	1	0	0	0	0	0	5	0	-	-	-	-
4	GHI Ltd	10	0	0	0	-	-	-	-	-	-	-	-	-	-	-	-	0	10	0	0	0	0	50	0
5	JKL Enterprise	0	1	0	1	0	2	0	2	-	-	-	-	0	1	0	0	6	0	0	0	-	-	-	-
6	MNO Ltd	-	-	-	-	-	-	-	-	-	-	-	-	0	0	2	0	0	0	0	8	-	-	-	-
Sheet1

 

[kweaver]

Welcome.

What result are you looking for? The sum of those three, how many have values (0), how many have "-" ??

 

[kweaver]

If you want the sum of those you specified, try this:

=SUM(INDEX($A2:$Y2,1,{5,7,19,25}))

 

[liloo]

Welcome.

What result are you looking for? The sum of those three, how many have values (0), how many have "-" ??

Hi kweaver,
I’m not looking for the sum. I want it to summarize each company’s purchase activity in text form.
My workbook has over 3000 rows and growing. I can’t think of a better way to do this.

 

[kweaver]

So, do you want the resulting cell to be a join of those period indicators that have numbers > 0?

 

[kweaver]

Is this what you're looking for (fill down):

=TEXTJOIN(", ",TRUE,FILTER($B$1:$Y$1,(B2:Y2>0)*(B2:Y2<>"-")))

 

[liloo]

So, do you want the resulting cell to be a join of those period indicators that have numbers > 0?

Yes, e.g. for ABC Inc (Row 2), I want to add a new cell/column at the end that says “2019Q4_A, 2019Q2_B, 2020Q2_B, 2020Q4_C". I need a formula that identifies those cells with a value other than 0 and -, and summarize them.
Thank you for helping! I’ve been thinking about it for over a week now and decided I should ask for help.

 

[liloo]

Is this what you're looking for (fill down):

=TEXTJOIN(", ",TRUE,FILTER($B$1:$Y$1,(B2:Y2>0)*(B2:Y2<>"-")))

Yes, this totally worked on my sample table, but it gave me "1" on my actual workbook. Is it because the cells B2:Y2 in my actual workbook are populated by formula?

 

[kweaver]

Having a formula in a cell shouldn't matter. What was the data in the row where you got a 1 as the result?

 

[liloo]

Yes, this totally worked on my sample table, but it gave me "1" on my actual workbook. Is it because the cells B2:Y2 in my actual workbook are populated by formula

Having a formula in a cell shouldn't matter. What was the data in the row where you got a 1 as the result?

Actually, it works! My eyes were deceiving me of the cell numbers. It gave me the results as expected. Thank you so much!!!