Average price of product based on product name.

[Ratters]

Hi all

I run a basic excel for keeping track of my product imports. The meat of it is "Product Name" "Unit Cost" after all the S&H, Tax math has been done.

My problem is that the unit cost varies with each order.

I use =countif to determine how many times the product name pops up in my sheet but wanted to see if i could turn that to into the average unit cost of that product.

Hope that makes sense.

Any help is appreciated.

Rgds

Marc

 

[Fluff]

Hi & welcome to MrExcel.
You can use the AVERAGEIFS function for that. AVERAGEIFS Function

 

[AhoyNC]

You could do a weighted average.
Something like this:

Book1
	A	B	C	D	E	F	G
1	Order Date	Product	Qty	Unit Cost			Avg. Cost
2	2/2/2023	ProdA	50	$ 5.00		ProdA	6.68
3	2/5/2023	ProdA	26	$ 5.00		ProdB	12.08
4	2/12/2023	ProdB	35	$ 10.00
5	3/2/2023	ProdB	42	$ 11.00
6	3/6/2023	ProdB	51	$ 11.00
7	3/10/2023	ProdA	65	$ 7.00
8	3/18/2023	ProdB	75	$ 11.00
9	3/19/2023	ProdB	12	$ 15.00
10	3/29/2023	ProdA	36	$ 7.00
11	4/1/2023	ProdA	28	$ 8.50
12	4/2/2023	ProdB	75	$ 15.00
13	4/20/2023	ProdA	24	$ 8.50
Sheet1

Cell Formulas
Range	Formula
G2:G3	G2	=SUMPRODUCT(--($B$2:$B$13=$F2),($C$2:$C$13),($D$2:$D$13))/SUMIF($B$2:$B$13,$F2,$C$2:$C$13)