sum the values based on three columns

[Arafat]

Hi All,

I have a worksheet which has 4 columns.
The first column contains name of person,
The second column contains project code(the same code repeats for other persons as well),
The third column has target values(This also repeats),
The fourth column has total hours spent on that day.

I want a macro/array formula to calculate total target.
I have this formula =SUM((IF(A:A="julie",C:C*D:D))/SUM(IF(A:A="julie",D:D))) to calculate the total target, but i can use this formula only after pivotin the worksheet then segregating the data for every person which consumes too much time Kindly help me.

 

[Aladin Akyurek]

If

[FONT=Lucida Console][SIZE=2]=SUM((IF(A:A="julie",C:C*D:D))/SUM(IF(A:A="julie",D:D)))[/SIZE][/FONT]

is correct, the following would be a tad faster:

Just enter...

=SUMPRODUCT(($A$2:$A$1000=K2)+0,$C$2:$C$1000,$D$2:$D$1000)/
   SUMIF($A$2:$A$1000,K2,$D$2:$D$1000)

 

[Arafat]

Thanks Alot.. It did wonders for me

 

[Aladin Akyurek]

Thanks Alot.. It did wonders for me

You are welcome. Thanks for providing feedback.