IF Formula

[eramirez148]

Is there a possible formula that will give me the following results in column A.
If the values from column B to column I are all zeros then the result should be zero. But if the values are higher than zero I want it to populate all the values except the zeros.

Column A	Column B	Column C	Column D	Column E	Column F	Column G	Column H	Column I
20, 19, 5, 8, 10	20​	19​	0​	0​	5​	8​	10​	0​
0​	0​	0​	0​	0​	0​	0​	0​	0​

 

[Cubist]

For D365 version, try

Book1
	A	B	C	D	E	F	G	H	I	J
1	20, 19, 5, 8, 10	20	19	0	0	5	8	10	0
2	0	0	0	0	0	0	0	0	0
3
4
Sheet4

Cell Formulas
Range	Formula
A1	A1	=LET(f,FILTER(B1:I1,B1:I1<>0),IFERROR(TEXTJOIN(", ",TRUE,f),0))
A2	A2	=LET(f,FILTER(B2:I2,B2:I2<>0),IFERROR(TEXTJOIN(",",TRUE,f),0))

 

[eramirez148]

For D365 version, try

Book1
	A	B	C	D	E	F	G	H	I	J
1	20, 19, 5, 8, 10	20	19	0	0	5	8	10	0
2	0	0	0	0	0	0	0	0	0
3
4
Sheet4

Cell Formulas
Range	Formula
A1	A1	=LET(f,FILTER(B1:I1,B1:I1<>0),IFERROR(TEXTJOIN(", ",TRUE,f),0))
A2	A2	=LET(f,FILTER(B2:I2,B2:I2<>0),IFERROR(TEXTJOIN(",",TRUE,f),0))

I have an additional issue,

I have additional columns in between the columns I want to consider in the formula how can I update the formula so that ignores the columns I do not want to include. For example I want to exclude columns B,B, F, H, J, L, N, P.

Column A

Column B

Column C

Column D

Column E

Column F

Column G

Column H

Column I

Column J

Column K

Column L

Column M

Column N

Column O

Column P

Column Q

20, 19, 5, 8, 10

5359.2​

20​

5369.2​

19​

5449.2​

0​

10449.2​

0​

4760.2​

5​

-10​

8​

89​

10​

10​

0​

0​

8​

0​

18​

0​

98​

0​

5098​

0​

-591​

0​

-10​

0​

89​

0​

10​

0​

 

[Cubist]

How about this?

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	20, 19, 5, 8, 10	5359	20	5359	19	5359	0	10449	0	4760.2	5	-10	8	89	10	10	0
2	0	8	0	18	0	98	0	5098	0	-591	0	-10	0	89	0	10	0
3
Sheet7

Cell Formulas
Range	Formula
A1:A2	A1	=LET(data,B1:Q1, f,FILTER(data,(MOD(COLUMN(data),2)*(data<>0))),IFERROR(TEXTJOIN(", ",TRUE, f),0))

 

[eramirez148]

This works. In if I happen to have an additional column in between can you provide a formula for that so I can keep it in my records

 

[Peter_SSs]

FWIW, with the initial layout there is no need for the IFERROR, and also no advantage in using LET in that circumstance.

24 04 07.xlsm
	A	B	C	D	E	F	G	H	I
1	20, 19, 5, 8, 10	20	19	0	0	5	8	10	0
2	0	0	0	0	0	0	0	0	0
TJ

Cell Formulas
Range	Formula
A1:A2	A1	=TEXTJOIN(", ",,FILTER(B1:I1,B1:I1<>0,0))

In relation to your follow-up questions: Do any of the columns have headings? If so, could we see the sample data including the headings?

 

[eramirez148]

FWIW, with the initial layout there is no need for the IFERROR, and also no advantage in using LET in that circumstance.

24 04 07.xlsm
	A	B	C	D	E	F	G	H	I
1	20, 19, 5, 8, 10	20	19	0	0	5	8	10	0
2	0	0	0	0	0	0	0	0	0
TJ

Cell Formulas
Range	Formula
A1:A2	A1	=TEXTJOIN(", ",,FILTER(B1:I1,B1:I1<>0,0))

In relation to your follow-up questions: Do any of the columns have headings? If so, could we see the sample data including the headings?

This is how the data will look including headings. Pretty much in column Root causes flagged. I want to populate the number on each root cause. The problem is that they will continue to add columns in between so if there is a way to specifically select only the root cause columns that would be great.

In the example below The root cause flagged column flagged 18,10,11,12 and if all were to be 0 then the results will be 0

Root causes Flagged	Number of instances product was out of stock	Root Cause	Initial Stock	Balance	Root Cause	Delivered Quantity against STO	Balance	Root Cause	STO Quantity	Balance	Root Cause	Store Order Forecast	Balance	Root Cause	Adjustments to available stock	Balance	Root Cause
18,10,11,12	0	18	7,209	47%	10	32,662	23%	11	32,662	23%	0	32,662	23%	12	-687	21%	0

 

[Cubist]

With Peter's comment and the header, it would've been easier if you showed the complete inquiry from the start. Peter might have other inputs.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	Root causes Flagged	Number of instances product was out of stock	Root Cause	Initial Stock	Balance	Root Cause	Delivered Quantity against STO	Balance	Root Cause	STO Quantity	Balance	Root Cause	Store Order Forecast	Balance	Root Cause	Adjustments to available stock	Balance	Root Cause
2	18, 10, 11, 12	0	18	7209	0.47	10	32662	0.23	11	32662	0.23	0	32662	0.23	12	-687	0.21	0
3	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
4
5
Sheet9

Cell Formulas
Range	Formula
A2:A3	A2	=TEXTJOIN(", ",,FILTER(B2:R2,($B$1:$R$1="Root Cause")*(B2:R2<>0),0))

 

[eramirez148]

With Peter's comment and the header, it would've been easier if you showed the complete inquiry from the start. Peter might have other inputs.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	Root causes Flagged	Number of instances product was out of stock	Root Cause	Initial Stock	Balance	Root Cause	Delivered Quantity against STO	Balance	Root Cause	STO Quantity	Balance	Root Cause	Store Order Forecast	Balance	Root Cause	Adjustments to available stock	Balance	Root Cause
2	18, 10, 11, 12	0	18	7209	0.47	10	32662	0.23	11	32662	0.23	0	32662	0.23	12	-687	0.21	0
3	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
4
Sheet9

Cell Formulas
Range	Formula
A2:A3	A2	=TEXTJOIN(", ",,FILTER(B2:R2,(B1:R1="Root Cause")*(B2:R2<>0),0))

Sorry about that but this work perfect.

One thing I notice is that the result is not in numerical value is this the case? or can the results be formatted as numerical value
.

 

[Cubist]

Not the way you want to show it i.e. numbers separated by comma.