List help with sumproduct formula

[Sinner]

Hiee,

With below mentioned calculations, I want to expand the PREreg list so
that I can add new items as array. Since it won't allow further
addition in list, I want to make a sheet named lists in which I can
use excel column to list down new items which can then be taken as an
array list names 'PREreg'. Can anyone help?

Formula used:
=SUMPRODUCT((ActivityList=PREreg)*(DateList=E$5))

Defined name is ActivityList =OFFSET(DateList,0,2)

Defined name is DateList =OFFSET(Data!$A$2,0,0,COUNT(Data!$A:$A),1)

Defined name is 'PREreg' which refers to: ={"a","b","Classic","Data
1","Data 2","Data 4","Economy","Economy
New","Exclusive","Favorite","Favorite New"}

Thanks

 

[paul.robinson]

Hi
Can't you define PREreg the same way you defined Datelist?
regards
Paul

 

[Bob Phillips]

Create a list and name it PREList (dynamically or however you want), and use

=SUMPRODUCT(--(ISNUMBER(MATCH(ActivityList,PREList,0))),--(DateList=E$5))

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Sinner]

Thanks BOB.
Done : )

Mind if I ask is it right enough to have it like this as it will take
lot of calculation time for a large list.
Any other way now to speed up.

Thx.

Sinner