Formula If duplicate do division sum - Excel 2010

[cferart]

Hi everyone
I can do this with a Pivot table but would like a formula for Col D the "Loans per copy" that does a calculation eg looks at Col B works out how many copies then adds Col C for that and then puts Loans per copy against ALL copies. Help appreciated, thanks

Excel 2010

A - Title	B - Copy no.	C - Loans	D- Loans per copy
Guide to English	1	11	8
Guide to English	2	5	8
Maths for beginners	1	2	4
Maths for beginners	2	9	4
Maths for beginners	3	0	4

<tbody>
</tbody>

 

[bbott]

Assuming your table starts in A1, try this in D2 and copy down:

=ROUNDUP(SUMIF($A$2:$A$6,A2,$C$2:$C$6)/COUNTIF($A$2:$A$6,A2),0)

 

[cferart]

Thanks so much bbott works perfectly.........

Assuming your table starts in A1, try this in D2 and copy down:

=ROUNDUP(SUMIF($A$2:$A$6,A2,$C$2:$C$6)/COUNTIF($A$2:$A$6,A2),0)

 

[bbott]

Thanks so much bbott works perfectly.........

You're welcome.