Excel Division Help

[Cruiser69]

Hi all.

Could anyone please help with a division query.

The Lots have been split into separate lines from the codes in Column C.
The total cost of the Lots combined are in Column E
Is there a way to divide the total cost by the number of items in the Lot
As there are 7 items within Lot 668, each item would be £17.14
I could divide each Lot but that would take a lot of time.
Is there a Formula or VBA for Column F which could do the job in less time.

A​	B​	C​	D​	E​	F​
LOT	DESCRIPTION	CODES	VENDOR	TOTAL COST	INDIVIDUAL COST
668	QTY OF ASSORTED DECORATIONS	4959522	JL-420	120	17.14
668	.	4929860	JL-420		17.14
668	.	4944710	JL-420		17.14
668	.	4957066	JL-420		17.14
668	.	4957498	JL-420		17.14
668	.	4831395	JL-420		17.14
668	.	4911201	JL-420		17.14
669	QTY OF ASSORTED FESTIVE LIGHTING	4931177	JL-420	87	17.40
669	.	4948200	JL-420		17.40
669	.	4944191	JL-420		17.40
669	.	4951716	JL-420		17.40
669	.	4957002	JL-420		17.40

<tbody>
</tbody>

Thanks for looking

Graham

 

[AlanY]

copy F2 down

Book1
	A	B	C	D	E	F
1	LOT	DESCRIPTION	CODES	VENDOR	TOTAL COST	INDIVIDUAL COST
2	668	QTY OF ASSORTED DECORATIONS	4959522	JL-420	120	17.14
3	668	.	4929860	JL-420		17.14
4	668	.	4944710	JL-420		17.14
5	668	.	4957066	JL-420		17.14
6	668	.	4957498	JL-420		17.14
7	668	.	4831395	JL-420		17.14
8	668	.	4911201	JL-420		17.14
9	669	QTY OF ASSORTED FESTIVE LIGHTING	4931177	JL-420	87	17.40
10	669	.	4948200	JL-420		17.40
11	669	.	4944191	JL-420		17.40
12	669	.	4951716	JL-420		17.40
13	669	.	4957002	JL-420		17.40
Sheet4

Cell Formulas
Range	Formula
F2	=SUMIF(A:A,A2,E:E)/COUNTIF(A:A,A2)

 

[Finalfight40]

Edit: Removed mine as Alan's is better

 

[Cruiser69]

Thanks a lot.

This is exactly what I was looking for

Regards,

Graham

 

[AlanY]

you're welome