Array Formula Syntax question...

[Steve P]

Hello,

I wrote the following formula
{=COUNT(IF(INDIRECT(B5&"!$D$2:$D$500")="CURRENT",IF(INDIRECT(B5&"!$E$2:$E$500")="UNRATED",INDIRECT(B5&"!$C$2:$C$500"))))}


The above formula works very well however, I'd like to change the "CURRENT"
parameter to an comparison operator range like: 1<x>30

How should the syntax work?

 

[Luke M]

Assuming you mean 1<x<30, we can multiply true/false checks to generate a
single output (note that this is still an array formula):

=COUNT(IF((INDIRECT(B5&"!$D$2:$D$500")>1)*(INDIRECT(B5&"!$D$2:$D$500")<30)*(INDIRECT(B5&"!$E$2:$E$500")="UNRATED"),INDIRECT(B5&"!$C$2:$C$500")))

Non-array version
=SUMPRODUCT((INDIRECT(B5&"!$D$2:$D$500")>1)*(INDIRECT(B5&"!$D$2:$D$500")<30)*(INDIRECT(B5&"!$E$2:$E$500")="UNRATED")*ISNUMBER(B5&"!$C$2:$C$500"))

 

[Steve P]

Thanks Luke, but I'm still getting an error message.

Should there be another coma in the formula?

 

[T. Valko]

I'm still getting an error message.

What kind of error message? What does it say?

Luke's COUNT formula works OK for me.

The SUMPRODUCT formula is missing the INDIRECT function in the ISNUMBER
array. Add INDIRECT and it also works.

=SUMPRODUCT((INDIRECT(B5&"!D2500")>1)*(INDIRECT(B5&"!D2500")<30)*(INDIRECT(B5&"!E2:E500")="UNRATED")*ISNUMBER(INDIRECT(B5&"!C2:C500")))

Tip: When using the INDIRECT function, *quoted cell references* are
evaluated as TEXT strings and will *never* change if the formula is copied.
The cell refs will *always* remain constant and in essence they are already
evaluated as being absolute references. So, you don't need to include the $
in the reference.