Same formula, errors on a different row

[shull82]

Hi everyone,

I'm having an issue with one of my formulas.
The formula below works when referencing data in row 13.
But when I try to use the same formula for row 18 I have a #DIV/0! error

=+SUMPRODUCT(--(INDIRECT("'"&CD$5&"'!$E$3:$XDF$3")<=$C$3),INDIRECT("'"&CD$5&"'!$E$13:$XDF$13"))

=+SUMPRODUCT(--(INDIRECT("'"&CD$5&"'!$E$3:$XDF$3")<=$C$3),INDIRECT("'"&CD$5&"'!$E$18:$XDF$18"))

Thank you for any help!

 

[KRice]

What happens if you try:

=SUMPRODUCT((INDIRECT("'"&CD$5&"'!$E$3:$XDF$3")<=$C$3)*(INDIRECT("'"&CD$5&"'!$E$18:$XDF$18")))

 

[shull82]

Now getting a #value! error for row 18.
I tested formula and row 13 and it worked just fine.
=SUMPRODUCT((INDIRECT("'"&CD$5&"'!$E$3:$XDF$3")<=$C$3)*(INDIRECT("'"&CD$5&"'!$E$13:$XDF$13")))

 

[KRice]

What is found in $E$18:$XDF$18? Are there any errors appearing in that range? In this version, I'm anticipating that an error might be found in that range...and then handling it by replacing it with 0. Does this work?

=SUMPRODUCT((INDIRECT("'"&CD$5&"'!$E$3:$XDF$3")<=$C$3)*(IFERROR(INDIRECT("'"&CD$5&"'!$E$18:$XDF$18"),0)))

 

[KRice]

To illustrate my point, I've mocked up a Sheet99 and referred to it within the INDIRECT formula. Sheet99 looks like this...showing only part of rows 3 and 18. Note that I've intentionally induced several error types for testing...and reordered the errors in several trials.

MrExcel_20231219.xlsx
	E	F	G	H	I	J	K	L	M	N	O
3	20	18	16	14	12	10	8	6	4	2
18	1	2	3	4	5	6	7	#VALUE!	#NUM!	#DIV/0!	#N/A
Sheet99

Cell Formulas
Range	Formula
E3:N3	E3	=SEQUENCE(,10,20,-2)
E18:K18	E18	=SEQUENCE(,7)
L18	L18	=XLOOKUP(19,E3:K3,E18:G18)
M18	M18	=LOG(-1)
N18	N18	=10/0
O18	O18	=XMATCH(19,E3,0)
Dynamic array formulas.

Then the formulas with and without error trapping are shown in CD7 and CD8:

MrExcel_20231219.xlsx
	C																																																																															CD	CE
3	11																																																																																threshold for logical condition
4
5																																																																																Sheet99	sheet reference
6
7																																																																																13	with error trapping
8																																																																																#VALUE!	without error trapping
Sheet4

Cell Formulas
Range	Formula
CD7	CD7	=SUMPRODUCT((INDIRECT("'"&CD$5&"'!$E$3:$XDF$3")<=$C$3)*(IFERROR(INDIRECT("'"&CD$5&"'!$E$18:$XDF$18"),0)))
CD8	CD8	=SUMPRODUCT((INDIRECT("'"&CD$5&"'!$E$3:$XDF$3")<=$C$3)*(INDIRECT("'"&CD$5&"'!$E$18:$XDF$18")))

Whichever error type is encountered first appears to be the type of error returned by the formula.