Issue with formula after converting to a dynamic range

[3G]

Hi there-

I have a formula that works perfectly fine with normal ranges. I've decided to add dynamic ranges so I don't have to manually update them when I paste the new data into the worksheet. However, since converting them, my formula doesn't work. If I convert one of them to a normal range, it's fine. The formula is as follows:


=SUMPRODUCT((SE_Owner=L21)*(FunSolRefID>0))


Where SE_Owner is all text, and, the FunSolRefID is a hodgepodge of numbers/letters/words.


The SE_Owner range is fine as:=OFFSET(Data!$AD$2,0,0,COUNTA(Data!$AD$2:$AD$6968),1)

while the FunsolRefID is the one giving me fits...

=OFFSET(Data!$AI$2,0,0,COUNTA(Data!$AI$2:$AI$6968),1)


Are there any hidden rules within a dynamic range? I know the use of COUNTA vs just COUNT is important. Regardless, I get an #N/A error.

 

[Debraj]

Hi 3G,


I think the hidden rule is residing in SUMPRODUCT..


and the rule is all array should be of same size..

=SUMPRODUCT(A2:A12="something")*(C2:C5="anything"))

will never gives you answer..


Regards,

Deb

 

[3G]

Thanks Deb!