Dynamic 'Defined Name' values within a formula not providing expected results

[grigby21]

Have a formula:
=SUMPRODUCT(COUNTIFS( INDIRECT("'"&$A$2&"'!$H$3:$H$99955"), $A$2 & UAT, INDIRECT("'"&$A$2&"'!$G$3:$G$99955"), $A$2 & Defect))

WHERE $A$2 refers to a dynamic value which serves as a prefix in the Defined Name naming convention.

Results being returned are not in agreement with the static search criteria.

Thoughts?

Thanks!

 

[Aladin Akyurek]

Thanks. I do have Defined Names created for conUAT, and conDefect... and when they are entered directly into the formula, it works fine. When I try to add the concatenated value, it is seemingly not recognized as a Defined Name.

Before proceeding further...

Control+shift+enter, not just enter:

=SUMPRODUCT(COUNTIFS( INDIRECT("'"&$A$2&"'!$H$3:$H$99955"), INDIRECT($A$2 & "UAT"), INDIRECT("'"&$A$2&"'!$G$3:$G$99955"), TRANSPOSE(INDIRECT($A$2 & "Defect")))

Does this work as you expect?

 

[grigby21]

Before proceeding further...

Control+shift+enter, not just enter:

=SUMPRODUCT(COUNTIFS( INDIRECT("'"&$A$2&"'!$H$3:$H$99955"), INDIRECT($A$2 & "UAT"), INDIRECT("'"&$A$2&"'!$G$3:$G$99955"), TRANSPOSE(INDIRECT($A$2 & "Defect")))

Does this work as you expect?

Thanks.... ...unfortunately, no.

 

[jkpieterse]

What do those UAT and Defect range names refer to? You may be having problems with implicit intersection not working whan using INDIRECT and hence the INDIRECT returning the entire array rather than the intersection between the current row (of the formula) with the array the range name points to.

 

[grigby21]

What do those UAT and Defect range names refer to? You may be having problems with implicit intersection not working whan using INDIRECT and hence the INDIRECT returning the entire array rather than the intersection between the current row (of the formula) with the array the range name points to.

They, combined with the $A$2 value refers to Defined Names. In my estimation, it seems the concatenated value is not being treated as a Defined Name, rather a literal text value.

 

[Aladin Akyurek]

Thanks.... ...unfortunately, no.

No, meaning wrong count or error?

And, care to post the contents of conUAT and conDefect?

Here is a workout...

Book1
	A	B	C	G	H	I	J	K	L
1								conUAT	conDefect
2	con	4		qad	a			a	kad
3				vad	a			b	lad
4				kad	b			c	nad
5				kad	c				vad
6				lad	a
7				qad	d
8				lad	e
9				lad	d
10
11
con

conUAT is a named range, referring to K2:K4 and conDefect a named range referring to L2:L5.

A2 = con

In B2 control+shift+enter, not just enter:

=SUMPRODUCT(COUNTIFS( INDIRECT("'"&$A$2&"'!$H$3:$H$99955"), INDIRECT($A$2 & "UAT"), INDIRECT("'"&$A$2&"'!$G$3:$G$99955"), TRANSPOSE(INDIRECT($A$2 & "Defect"))))

Is the foregoing analogical/comparable to your situation?

 

[grigby21]

No, meaning wrong count or error?

And, care to post the contents of conUAT and conDefect?

Here is a workout...

	A	B	C	G	H	I	J	K	L
conUAT	conDefect
con	qad	a	a	kad
vad	a	b	lad
kad	b	c	nad
kad	c	vad
lad	a
qad	d
lad	e
lad	d

<colgroup><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]4[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

con​

conUAT is a named range, referring to K2:K4 and conDefect a named range referring to L2:L5.

A2 = con

In B2 control+shift+enter, not just enter:

=SUMPRODUCT(COUNTIFS( INDIRECT("'"&$A$2&"'!$H$3:$H$99955"), INDIRECT($A$2 & "UAT"), INDIRECT("'"&$A$2&"'!$G$3:$G$99955"), TRANSPOSE(INDIRECT($A$2 & "Defect"))))

Is the foregoing analogical/comparable to your situation?

Yes this is very similar to my current situation. However I am presently getting a count of 0. If I change the calls of the Defined Names to their actual name (conUAT, conDefect), counts are returned.

 

[Aladin Akyurek]

Yes this is very similar to my current situation. However I am presently getting a count of 0. If I change the calls of the Defined Names to their actual name (conUAT, conDefect), counts are returned.

As I tried to demonstrate, INDIRECT(A2&"UAT") will return the contents of conUAT where A2 = con to the formula it's part of...

By the way, control+shift+enter means: Press down the control and the shift keys at the same while you hit the enter key. If done successfully, Excel itself puts a pair of { and } around the formula in recognition.

 

[grigby21]

As I tried to demonstrate, INDIRECT(A2&"UAT") will return the contents of conUAT where A2 = con to the formula it's part of...

By the way, control+shift+enter means: Press down the control and the shift keys at the same while you hit the enter key. If done successfully, Excel itself puts a pair of { and } around the formula in recognition.

I'll try again; however when attempting the same as noted above I was not receiving any count.

 

[Aladin Akyurek]

I'll try again; however when attempting the same as noted above I was not receiving any count.

See the workbook for the implementation: https://www.dropbox.com/s/yl643jxv0h0jiyh/grigby21%20sumproduct%2Bcountifs%2Bindirect.xlsx?dl=0

 

[grigby21]

See the workbook for the implementation: https://www.dropbox.com/s/yl643jxv0h0jiyh/grigby21 sumproduct+countifs+indirect.xlsx?dl=0

Thanks! ...though unable to access for some reason. What I did learn, is that it seems the definition of my Defined Names is not being handled properly. When I changed the 'Refers To' from: =OFFSET('Mapping - Defects'!$O$7,0,0,COUNTA('Mapping - Defects'!$O$7:'Mapping - Defects'!$O$23),1) to an explicit definition: ='Mapping - Defects'!$O$7:$O$13 ...the formula worked. Sigh.