improve formula offset and indirect

Hi!

IF you used dynamic named ranges:

Normally entered:

=SUMPRODUCT(--(Rng1=DUDListData!$Y$1),--(Rng2=Summary!$G$2))/COUNTIF(Rng2,Summary!$G$2)

Biff

"John Contact" <JohnContact@discussions.microsoft.com> wrote in message
news:34EBB9BD-3E55-4023-80B3-944A9515F376@microsoft.com...
> Hi,
>
> Can someone have a look at the below formula and some ideas on how to
> improve the arrays, I understand I should be using the offset and indirect
> to
> find the exact range to improve performance, however not to sure where to
> brgin with this.
>
> =SUM(IF((DUDListData!$T$2:$T$65536=DUDListData!$Y$1)*(DUDListData!$U$2:$U$65536=Summary!$G$2)=1,1,0))/SUM(IF(DUDListData!$U$2:$U$65536=Summary!$G$2,1,0))
>
> Many thanks John

Hi,

Can someone have a look at the below formula and some ideas on how to
improve the arrays, I understand I should be using the offset and indirect to
find the exact range to improve performance, however not to sure where to
brgin with this.

=SUM(IF((DUDListData!$T$2:$T$65536=DUDListData!$Y$1)*(DUDListData!$U$2:$U$65536=Summary!$G$2)=1,1,0))/SUM(IF(DUDListData!$U$2:$U$65536=Summary!$G$2,1,0))

Many thanks John

Hi!

IF you used dynamic named ranges:

Normally entered:

=SUMPRODUCT(--(Rng1=DUDListData!$Y$1),--(Rng2=Summary!$G$2))/COUNTIF(Rng2,Summary!$G$2)

Biff

"John Contact" <JohnContact@discussions.microsoft.com> wrote in message
news:34EBB9BD-3E55-4023-80B3-944A9515F376@microsoft.com...
> Hi,
>
> Can someone have a look at the below formula and some ideas on how to
> improve the arrays, I understand I should be using the offset and indirect
> to
> find the exact range to improve performance, however not to sure where to
> brgin with this.
>
> =SUM(IF((DUDListData!$T$2:$T$65536=DUDListData!$Y$1)*(DUDListData!$U$2:$U$65536=Summary!$G$2)=1,1,0))/SUM(IF(DUDListData!$U$2:$U$65536=Summary!$G$2,1,0))
>
> Many thanks John