sum/division calculation involving vlookup with array?

[markx]

Hi guys,



I have the following table (more or less 1000 rows):



Code1 Code2 Number1

510 632 48

.. . .

555 222 24

555 186 60

555 345 18

555 841 32

555 111 120

555 428 96

.. . .

575 334 6

.. . .



Then, I run the reports that can give me the f. ex. following input:



Code1 Number2

555 44



Now starts the 'tricky' part. What I would like is to enter these values
somewhere in excel and get:



Code1 Number2 Calculation

555 44 xxx



Where 'xxx' corresponds to:

=44/24+44/60+44/18+44/32+44/120+44/96



'Number2' is divided (one by one) by all the 'Number1' for the 'Code1' (555
in our example), and then summed up.



Do you have any idea how to achieve this?

Thanks a lot in advance, it would be of big help to me.



Regards,

Mark

 

[Bernie Deitrick]

Mark,

=SUMPRODUCT(($A$1:$A$1000=CellWithThe555)*CellWithThe44/$C$1:$C$1000)

HTH,
Bernie
MS Excel MVP

 

[markx]

Thanks, it works!

Mark,

=SUMPRODUCT(($A$1:$A$1000=CellWithThe555)*CellWithThe44/$C$1:$C$1000)

HTH,
Bernie
MS Excel MVP