LookUp or?

[kandila888]

HI,

I am trying to make a summation of how many items had been sold in a month but the items have different prices. Table format is below. I combined all transaction for 2017 and made a subtotal for each of the month.

[TABLE="width: 500"]
<tbody>[TR]
[TD]year[/TD]
[TD]Month[/TD]
[TD]Date[/TD]
[TD]Customer[/TD]
[TD]Product[/TD]
[TD]Quantity[/TD]
[TD]Given Price per Unit [/TD]
[TD]Subtotal
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2017[/TD]
[TD]Jan[/TD]
[TD]1-1-2017[/TD]
[TD]a[/TD]
[TD]ABC[/TD]
[TD]5[/TD]
[TD]20[/TD]
[TD]=Quantity*PriceperUnit[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2017[/TD]
[TD]Jan[/TD]
[TD]23-1-2017[/TD]
[TD]d[/TD]
[TD]fsdf[/TD]
[TD]7[/TD]
[TD]215[/TD]
[TD]=Quantity*PriceperUnit[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2017[/TD]
[TD]Jan[/TD]
[TD]30-1-2017[/TD]
[TD]d[/TD]
[TD]ABC[/TD]
[TD]9[/TD]
[TD]17
[/TD]
[TD][TABLE="width: 500"]
<tbody>[TR]
[TD]=Quantity*PriceperUnit

[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Total Jan[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2017[/TD]
[TD]Feb[/TD]
[TD]2-2-2017[/TD]
[TD]g[/TD]
[TD]DFG[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

from the above table I want to know what formula I can use to know how many ABC had been sold and how much is the amount. and what if there the price per unit is fixed but there is a discount which formula should I use to fill another table as below?


[TABLE="width: 500"]
<tbody>[TR]
[TD]Products[/TD]
[TD]ABC[/TD]
[TD]Sales$[/TD]
[TD]SDF[/TD]
[TD]Sales $[/TD]
[TD]DFG[/TD]
[TD]Sales$[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Month[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jan[/TD]
[TD]14[/TD]
[TD]253[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Feb[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]March[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


And if unit price is fixed but different discounts. --- so total of the discount for example product ABC needs to be summed up.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Product[/TD]
[TD]ABC[/TD]
[TD]Quantity[/TD]
[TD]Subtotal[/TD]
[TD]Less Discount[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jan[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Feb[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]March[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]to Dec down[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


thank you for the help.

 

[jorismoerings]

Hi,

Your discount question can't be answered without a clear statement of what you're using as a gross price. You're only mentioning Price given.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	year	Month	Date	Customer	Product	Quantity	Given Price per Unit	Subtotal		Products	ABC	Sales$	SDF	Sales $	DFG	Sales$
2	2017	Jan	1-1-2017	a	ABC	5	20	100		Month
3	2017	Jan	23-1-2017	d	SDF	7	215	1505		Jan	14	253	7	1505	0	0
4	2017	Jan	30-1-2017	d	ABC	9	17	153		Feb	0	0	0	0	7	1505
5										March
6	2017	Feb	2-2-2017	g	DFG	7	215	1505
Sheet1

Cell Formulas
Range	Formula
K3	=SUMIFS($F$2:$F$6,$E$2:$E$6,K$1,$B$2:$B$6,$J3)
L3	=SUMIFS($H$2:$H$6,$E$2:$E$6,K$1,$B$2:$B$6,$J3)
M3	=SUMIFS($F$2:$F$6,$E$2:$E$6,M$1,$B$2:$B$6,$J3)
N3	=SUMIFS($H$2:$H$6,$E$2:$E$6,M$1,$B$2:$B$6,$J3)
O3	=SUMIFS($F$2:$F$6,$E$2:$E$6,O$1,$B$2:$B$6,$J3)
P3	=SUMIFS($H$2:$H$6,$E$2:$E$6,O$1,$B$2:$B$6,$J3)