sum/division calculation involving vlookup with array?
 

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

sum/division calculation involving vlookup with array?
 

Mark,

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

HTH,
Bernie
MS Excel MVP


"markx" wrote in message
...
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

sum/division calculation involving vlookup with array?
 

Thanks, it works!

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Mark,

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

HTH,
Bernie
MS Excel MVP


"markx" wrote in message
...
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