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.

year	Month	Date	Customer	Product	Quantity	Given Price per Unit	Subtotal
2017	Jan	1-1-2017	a	ABC	5	20	=Quantity*PriceperUnit
2017	Jan	23-1-2017	d	fsdf	7	215	=Quantity*PriceperUnit
2017	Jan	30-1-2017	d	ABC	9	17	=Quantity*PriceperUnit <tbody> </tbody>
Total Jan
2017	Feb	2-2-2017	g	DFG

<tbody>
</tbody>

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?

Products	ABC	Sales$	SDF	Sales $	DFG	Sales$
Month
Jan	14	253
Feb
March

<tbody>
</tbody>

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

Product	ABC	Quantity	Subtotal	Less Discount
Jan
Feb
March
to Dec down

<tbody>
</tbody>

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)