Array Formula Syntax question...
 

Hello,

I wrote the following formula:
{=COUNT(IF(INDIRECT(B5&"!$D$2:$D$500")="CURRENT",I F(INDIRECT(B5&"!$E$2:$E$500")="UNRATED",INDIRECT(B 5&"!$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<x30

How should the syntax work?

Array Formula Syntax question...
 

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)*(INDIREC T(B5&"!$D$2:$D$500")<30)*(INDIRECT(B5&"!$E$2:$E$50 0")="UNRATED"),INDIRECT(B5&"!$C$2:$C$500")))

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

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Steve P" wrote:

Hello,

I wrote the following formula:
{=COUNT(IF(INDIRECT(B5&"!$D$2:$D$500")="CURRENT",I F(INDIRECT(B5&"!$E$2:$E$500")="UNRATED",INDIRECT(B 5&"!$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<x30

How should the syntax work?

Array Formula Syntax question...
 

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

Should there be another coma in the formula?

"Luke M" wrote:

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)*(INDIREC T(B5&"!$D$2:$D$500")<30)*(INDIRECT(B5&"!$E$2:$E$50 0")="UNRATED"),INDIRECT(B5&"!$C$2:$C$500")))

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

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Steve P" wrote:

Hello,

I wrote the following formula:
{=COUNT(IF(INDIRECT(B5&"!$D$2:$D$500")="CURRENT",I F(INDIRECT(B5&"!$E$2:$E$500")="UNRATED",INDIRECT(B 5&"!$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<x30

How should the syntax work?

Array Formula Syntax question...
 

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&"!D2:D500")1)*(INDIRECT( B5&"!D2:D500")<30)*(INDIRECT(B5&"!E2:E500")="UNRAT ED")*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.


--
Biff
Microsoft Excel MVP


"Steve P" wrote in message
...
Thanks Luke, but I'm still getting an error message.

Should there be another coma in the formula?

"Luke M" wrote:

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)*(INDIREC T(B5&"!$D$2:$D$500")<30)*(INDIRECT(B5&"!$E$2:$E$50 0")="UNRATED"),INDIRECT(B5&"!$C$2:$C$500")))

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

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Steve P" wrote:

Hello,

I wrote the following formula:
{=COUNT(IF(INDIRECT(B5&"!$D$2:$D$500")="CURRENT",I F(INDIRECT(B5&"!$E$2:$E$500")="UNRATED",INDIRECT(B 5&"!$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<x30

How should the syntax work?