Array Formula Syntax question...

Steve P · External Since: Jun 01, 2006 Posts: 5

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 · External Since: Jan 08, 2009 Posts: 187

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"))

--
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",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?

Steve P · External Since: Jun 01, 2006 Posts: 5

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)*(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"))
>
> --
> 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",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?

T. Valko · External Since: Nov 24, 2006 Posts: 3429

>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")="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.

--
Biff
Microsoft Excel MVP

"Steve P" <SteveP RemoveThis @discussions.microsoft.com> wrote in message
news:9DA7BC6D-3777-409F-BC99-B0B0A5B2448C@microsoft.com...
> 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)*(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"))
>>
>> --
>> 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",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?