How to Create Sum Product in Many Creteria

[Joko Purnomo]

Source Data

Fruit	Amount	Quantity
Apple	128.038	24​
Pineapple	79.545	7​
Mango	82.161	24​
Guava	36.993	24​
Pomegranate	14.661	106​
Banana	8.200	32​
Watermelon	323.207	3​
Dragon fruit	16.691	4​
Avocado	80.599	23​
Strawberry	23.970	31​
Blueberry	859.813	23​
Blackberry	1.302	4​
Blackcurrant	954.597	4​
Raspberry	93.610	14​
Cranberry	827.543	7​
Guava	68.991	40​
Pomegranate	953.697	159​
Banana	661.039	4​
Watermelon	916.716	27​
Dragon fruit	93.950	4​
Avocado	444.607	34​
Strawberry	812.470	61​
Blueberry	491.406	26​
Blackberry	41.430	164​

Dashboard

Fruit	Amount (Quantity > = 4 dan < 50)	Amount (Quantity > = 4 dan < 200)
Apple	?	?
Pineapple
Mango
Guava
Pomegranate
Banana
Watermelon
Dragon fruit
Avocado
Strawberry
Blueberry
Blackberry
Blackcurrant
Raspberry
Cranberry

How to fill the blank dashboard above with formula Source Data, i want to sum the amount with criteria (Quantity > = 4 dan < 50) and the amount with criteria (Quantity > = 4 dan < 200)

Thank for helping

 

[Skybluekid]

Try the below:

Book1
	A	B	C	D	E	F	G	H	I
1
2		Fruit	Amount	Quantity			Amount (Quantity > = 4 dan < 50)	Amount (Quantity > = 4 dan < 200)
3		Apple	128.038	24		Apple	0	128.038
4		Pineapple	79.545	7		Pineapple	0	79.545
5		Mango	82.161	24		Mango	0	82.161
6		Guava	36.993	24		Guava	36.993	105.984
7		Pomegranate	14.661	106		Pomegranate	14.661	14.661
8		Banana	8.2	32		Banana	8.2	8.2
9		Watermelon	323.207	3		Watermelon	0	0
10		Dragon fruit	16.691	4		Dragon fruit	16.691	110.641
11		Avocado	80.599	23		Avocado	0	80.599
12		Strawberry	23.97	31		Strawberry	23.97	23.97
13		Blueberry	859.813	23		Blueberry	0	0
14		Blackberry	1.302	4		Blackberry	42.732	42.732
15		Blackcurrant	954.597	4		Blackcurrant	0	0
16		Raspberry	93.61	14		Raspberry	0	93.61
17		Cranberry	827.543	7		Cranberry	0	0
18		Guava	68.991	40
19		Pomegranate	953.697	159
20		Banana	661.039	4
21		Watermelon	916.716	27
22		Dragon fruit	93.95	4
23		Avocado	444.607	34
24		Strawberry	812.47	61
25		Blueberry	491.406	26
26		Blackberry	41.43	164
27
Sheet1

Cell Formulas
Range	Formula
G3:G17	G3	=SUMIFS(Dan,Fruit,F3,Dan,"<50",Qty,">=4")
H3:H17	H3	=SUMIFS(Dan,Fruit,F3,Dan,"<200",Qty,">=4")

Named Ranges
Name	Refers To	Cells
Dan	=Sheet1!$C$3:$C$26	G3:H17
Fruit	=Sheet1!$B$3:$B$26	G3:H17
Qty	=Sheet1!$D$3:$D$26	G3:H17

 

[Fluff]

Your question is a bit confusing as you mention dan as one of the criteria, but there is nothing to say what dan is.
At a guess maybe.

Fluff.xlsm
	A	B	C	D	E	F	G	H
1	Fruit	Amount	Quantity			Fruit	Amount (Quantity > = 4 dan < 50)	Amount (Quantity > = 4 dan < 200)
2	Apple	128.038	24			Apple	128.038	128.038
3	Pineapple	79.545	7			Pineapple	79.545	79.545
4	Mango	82.161	24			Mango	82.161	82.161
5	Guava	36.993	24			Guava	105.984	105.984
6	Pomegranate	14.661	106			Pomegranate	0	968.358
7	Banana	8.2	32			Banana	669.239	669.239
8	Watermelon	323.207	3			Watermelon	916.716	916.716
9	Dragon fruit	16.691	4			Dragon fruit	110.641	110.641
10	Avocado	80.599	23			Avocado	525.206	525.206
11	Strawberry	23.97	31			Strawberry	23.97	836.44
12	Blueberry	859.813	23			Blueberry	1351.219	1351.219
13	Blackberry	1.302	4			Blackberry	1.302	42.732
14	Blackcurrant	954.597	4			Blackcurrant	954.597	954.597
15	Raspberry	93.61	14			Raspberry	93.61	93.61
16	Cranberry	827.543	7			Cranberry	827.543	827.543
17	Guava	68.991	40
18	Pomegranate	953.697	159
19	Banana	661.039	4
20	Watermelon	916.716	27
21	Dragon fruit	93.95	4
22	Avocado	444.607	34
23	Strawberry	812.47	61
24	Blueberry	491.406	26
25	Blackberry	41.43	164
Data

Cell Formulas
Range	Formula
G2:G16	G2	=SUMIFS(B:B,A:A,F2,C:C,">=4",C:C,"<50")
H2:H16	H2	=SUMIFS(B:B,A:A,F2,C:C,">=4",C:C,"<200")

 

[Joko Purnomo]

Your question is a bit confusing as you mention dan as one of the criteria, but there is nothing to say what dan is.
At a guess maybe.

Fluff.xlsm
	A	B	C	D	E	F	G	H
1	Fruit	Amount	Quantity			Fruit	Amount (Quantity > = 4 dan < 50)	Amount (Quantity > = 4 dan < 200)
2	Apple	128.038	24			Apple	128.038	128.038
3	Pineapple	79.545	7			Pineapple	79.545	79.545
4	Mango	82.161	24			Mango	82.161	82.161
5	Guava	36.993	24			Guava	105.984	105.984
6	Pomegranate	14.661	106			Pomegranate	0	968.358
7	Banana	8.2	32			Banana	669.239	669.239
8	Watermelon	323.207	3			Watermelon	916.716	916.716
9	Dragon fruit	16.691	4			Dragon fruit	110.641	110.641
10	Avocado	80.599	23			Avocado	525.206	525.206
11	Strawberry	23.97	31			Strawberry	23.97	836.44
12	Blueberry	859.813	23			Blueberry	1351.219	1351.219
13	Blackberry	1.302	4			Blackberry	1.302	42.732
14	Blackcurrant	954.597	4			Blackcurrant	954.597	954.597
15	Raspberry	93.61	14			Raspberry	93.61	93.61
16	Cranberry	827.543	7			Cranberry	827.543	827.543
17	Guava	68.991	40
18	Pomegranate	953.697	159
19	Banana	661.039	4
20	Watermelon	916.716	27
21	Dragon fruit	93.95	4
22	Avocado	444.607	34
23	Strawberry	812.47	61
24	Blueberry	491.406	26
25	Blackberry	41.43	164
Data

Cell Formulas
Range	Formula
G2:G16	G2	=SUMIFS(B:B,A:A,F2,C:C,">=4",C:C,"<50")
H2:H16	H2	=SUMIFS(B:B,A:A,F2,C:C,">=4",C:C,"<200")

Thanks very much

 

[Joko Purnomo]

How about countifs condition for column G (Quantity > = 4 dan < 50) and column H (Quantity > = 4 dan < 200), we whan know the total quantity for the creteria???

Help me master

 

[Skybluekid]

How about countifs condition for column G (Quantity > = 4 dan < 50) and column H (Quantity > = 4 dan < 200), we whan know the total quantity for the creteria???

Help me master

You just replace SUMIFS with COUNTIFS and use the same criteria except the first element:

=COUNTIFS(A:A,F2,C:C,">=4",C:C,"<50")

 

[Joko Purnomo]

Why pivot table of amount, which discount 5%, the result is same, i add new column beside the pivot column, with formula (=5%*GETPIVOTDATA("Amount (Quantity > = 4 dan < 200)";$B5;"Fruit";"Apple")) and the result is same

And one case again, when i do value filter top 10 on the vipottable, the formula the right besides tobe error

help me

 

[Fluff]

Did either the two formulae posted do what you originally asked for?

 

[Joko Purnomo]

Yes sir, the last posting of my question is a part of my problem, two questions before is solved, the last i want to combine vipot table, and other formula beside vipot table, buat i don't know why the result is same. Like the screenshot above, i add new column (Discount 5%) of each column from the vipot table, and result is same, i know its wrong, but i don't find why by my self?

Other side, when i do value filter on vipot table to be top 10, the formula the column beside vipot table tobe error.

Is possible the pivot table result to connect another formula beside ? If possible please help me sir, to solve my problem

Thank you for your support and answer


Best regards

 

[Fluff]

As this is a significantly different question, it needs a new thread. Thanks