IF(OR(ISBLANK Formula that is part of a Spill Array

[ScottTemple]

Hello,

I have a spill array in tab2 of my workbook as follows:

=LET(a,CHOOSECOLS(Shaver_Data,SEQUENCE(30)),IF(a="","",a))

I have a formula in cell AX:

=(INDEX(A2#,,22)+(INDEX(A2#,,27)))

However, if either 22 or 27 is missing it's value/blank, I need to have the calculation still continue, currently it returns a #VALUE! error in the cell. I have attempted the following formula, but receive an error message:

=IF(OR(ISBLANK(INDEX(A2#,,22),ISBLANK(INDEX(A2#,,27)),"",(INDEX(A2#,,22)+(INDEX(A2#,,27)))

 

[awoohaw]

What is the relation between the two statements.
Is the array in A2#?

How is a 22 or 27 missing from a sequence?

 

[Fluff]

Try

=sum(INDEX(A2#,,22),INDEX(A2#,,27))

 

[ScottTemple]

Try

=sum(INDEX(A2#,,22),INDEX(A2#,,27))

Hey Fluff,

That works, but it doesn't spill.

 

[ScottTemple]

What is the relation between the two statements.
Is the array in A2#?

How is a 22 or 27 missing from a sequence?

Sorry Awoohaw, yes the A2# is the spill range that goes from column A to AD. The ISBLANK formula I'm looking to create is contained outside of that spill range in column AX.

 

[awoohaw]

Okay, but how is 22 or 27 ever missing from a sequence of 1-30?

 

[ScottTemple]

Okay, but how is 22 or 27 ever missing from a sequence of 1-30?

They're not, the data in the cells from columns that represent 22 or 27 could be missing. So if I have 100 rows from the spill array and 5 of those rows have a blank in the cells in either column 22 (V) or 27 (AA), I would still want to calculate the total.

 

[awoohaw]

you want the total of the rows?

try this:
although this has no blank cells, it wil work.

Book2
	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	Z	AA	AB	AC	AD	AE																			AX	AY
1
2	2	1	3	9	7	8	1	3	5	5	1	10	8	2	4	3	2	6	5	6	10	4	8	2	4	2	10	6	9	5																				151
3	2	6	5	4	4	3	6	5	1	5	3	7	2	5	3	8	3	6	1	7	6	7	3	10	7	3	8	2	1	6																				139
4	9	10	6	1	1	6	10	8	6	9	4	5	1	7	10	5	8	8	8	6	2	5	10	6	1	3	5	6	4	3																				173
5	1	2	2	10	7	10	5	5	6	4	8	2	1	9	1	8	1	8	9	4	2	4	10	5	5	10	2	5	3	3																				152
6	3	8	6	1	5	5	1	4	7	10	6	4	2	5	2	3	10	10	5	1	9	9	1	7	2	5	10	8	5	2																				156
7
Sheet1

Cell Formulas
Range	Formula
A2:AD6	A2	=RANDARRAY(5,30,1,10,1)
AX2:AX6	AX2	=LET(a,$A$2#,aa,IF(a="",0,a),BYROW(aa,LAMBDA(aa,SUM(aa))))
Dynamic array formulas.

 

[ScottTemple]

you want the total of the rows?

Yes, I would like to have the total of V and AA, or just either V or AA, if one or the other is blank, or, return a blank value if both V and AA are blank.

 

[awoohaw]

small correction:

=LET(a,$A$2:$AD$6,aa,IF(a="",0,a),BYROW(aa,LAMBDA(aa,SUM(aa))))

unless the array is already defined in your worksheet:
or as above:

=LET(a,$A$2#,aa,IF(a="",0,a),BYROW(aa,LAMBDA(aa,SUM(aa))))