Need help to create avb function

[Bokazoit]

I need a function that can do the following:

The function reads a textstring from sheet1, that look as follows - lets call that variable var1:

var1: DK10, DK12,...,DK30,...,DK65

In coloumn Q I have var1 represented in random amounts depending on search criterias. In coloumn P I have another set of strings - storenumbers (lets call that variable StoreNb), looking like this:

DK70101
DK10004
DK60105
DK60125
DK20501
DK30001
DK70102
DK10006
DK10002
DK10001
etc.

What the function should do is to find var1 (i.e. DK10) and then count the amount of stores (i.e. DK70101).

Finally each count should be multiplied with a cell in sheet1 that has a relation to StoreNb.

So if coloumn P and Q contains this:

DK10 DK70101
DK10 DK70101
DK10 DK70101
DK10 DK70101
DK10 DK10001
DK10 DK10001
DK10 DK10001
DK10 DK10001
DK10 DK10001
etc.

The function should look for DK10 in coloumn Q then count DK70101 in coloumn P (4 in the example) and then multiply that result with lets say cell $G$24, then it counts DK10001 in coloumn P and multiply that result with cell $G$27

So if cell G24=1 and G27=3 the function should return:

function(var1)=search(DK10(count(DK70101)*$G$24+count(DK10001)*$G$27)=4*1+5*3=19

In advance tx

 

[xlbo]

=SUMPRODUCT(($Q$1:$Q$10000="DK10")*($P$1:$P$10000="DK70101")*($G$24)) + SUMPRODUCT(($Q$1:$Q$10000="DK10")*($P$1:$P$10000="DK10001")*($G$27))

should do the trick for you

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[Bokazoit]

tx very much that did the work...The only problem is that I do not hope that there will be more stores 'cause then excel can't handle the amount of lines in the formula