Sumproduct Multiple criteria same column

[vsr_shimla]

Pls help I want to find the Weighted average of the following data in a single column :
I have tried Sumproduct(--(Heads="Freight")*(Value),(Heads="Sale")*(Value))/Sum(Sale)

COUNTY Heads Value
A Sale 200
A Freight 35
A Distance 50
B Sale 300
B Freight 50
B Distance 100
C Sale 200
C Freight 35
C Distance 50


Regards

 

[DanteAmor]

Welcome to the forum!

According to your example, what values ​​should be added and what is the expected result?

 

[strooman]

What's the weight (factor)?

Excel 2016 (Windows) 64 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td=bgcolor:#0070C0]COUNTY[/td][td=bgcolor:#0070C0]Heads[/td][td=bgcolor:#0070C0]Value[/td][td=bgcolor:#0070C0]Weight[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td]A[/td][td]Sale[/td][td]

200​

[/td][td]

?​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td]A[/td][td]Freight[/td][td]

35​

[/td][td]

?​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td]A[/td][td]Distance[/td][td]

50​

[/td][td]

?​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td]B[/td][td]Sale[/td][td]

300​

[/td][td]

?​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6​

[/td][td]B[/td][td]Freight[/td][td]

50​

[/td][td]

?​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

7​

[/td][td]B[/td][td]Distance[/td][td]

100​

[/td][td]

?​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

8​

[/td][td]C[/td][td]Sale[/td][td]

200​

[/td][td]

?​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

9​

[/td][td]C[/td][td]Freight[/td][td]

35​

[/td][td]

?​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

10​

[/td][td]C[/td][td]Distance[/td][td]

50​

[/td][td]

? ​

[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]

 

[AhoyNC]

As pointed out by DanteAmor it would help to know the expected result.
But Maybe?

Excel Workbook
	A	B	C	D	E	F
1	COUNTY	Heads	Value
2	A	Sale	200
3	A	Freight	35
4	A	Distance	50			Wt. Average
5	B	Sale	300			136.6666667
6	B	Freight	50
7	B	Distance	100
8	C	Sale	200
9	C	Freight	35
10	C	Distance	50
Sheet

 

[vsr_shimla]

Welcome to the forum!

According to your example, what values ​​should be added and what is the expected result?

The Wgt Avg Freight will be 41.43 & Distance will be 71.43 as Sales is the weight.

 

[vsr_shimla]

The Wgt Avg Freight will be 41.43 & Distance will be 71.43 as Sales is the weight.

What's the weight (factor)?

Excel 2016 (Windows) 64 bit
[TABLE="class: head"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[TH][/TH]
[TH]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]A[/COLOR]​

[/TH]
[TH]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]B[/COLOR]​

[/TH]
[TH]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]C[/COLOR]​

[/TH]
[TH]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]D[/COLOR]​

[/TH]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]1[/COLOR]​

[/TD]
[TD="bgcolor: #0070C0"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]COUNTY[/COLOR][/TD]
[TD="bgcolor: #0070C0"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Heads[/COLOR][/TD]
[TD="bgcolor: #0070C0"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Value[/COLOR][/TD]
[TD="bgcolor: #0070C0"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Weight[/COLOR][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]2[/COLOR]​

[/TD]
[TD]A[/TD]
[TD]Sale[/TD]
[TD]

200​

[/TD]
[TD]

?​

[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]3[/COLOR]​

[/TD]
[TD]A[/TD]
[TD]Freight[/TD]
[TD]

35​

[/TD]
[TD]

?​

[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]4[/COLOR]​

[/TD]
[TD]A[/TD]
[TD]Distance[/TD]
[TD]

50​

[/TD]
[TD]

?​

[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]5[/COLOR]​

[/TD]
[TD]B[/TD]
[TD]Sale[/TD]
[TD]

300​

[/TD]
[TD]

?​

[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]6[/COLOR]​

[/TD]
[TD]B[/TD]
[TD]Freight[/TD]
[TD]

50​

[/TD]
[TD]

?​

[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]7[/COLOR]​

[/TD]
[TD]B[/TD]
[TD]Distance[/TD]
[TD]

100​

[/TD]
[TD]

?​

[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]8[/COLOR]​

[/TD]
[TD]C[/TD]
[TD]Sale[/TD]
[TD]

200​

[/TD]
[TD]

?​

[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]9[/COLOR]​

[/TD]
[TD]C[/TD]
[TD]Freight[/TD]
[TD]

35​

[/TD]
[TD]

?​

[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]10[/COLOR]​

[/TD]
[TD]C[/TD]
[TD]Distance[/TD]
[TD]

50​

[/TD]
[TD]

? ​

[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid"]
<tbody>[TR]
[TD]Sheet: Sheet1[/TD]
[/TR]
</tbody>[/TABLE]

Thanx .
If the data is not in the same column it would be like this

[TABLE="width: 256"]
<colgroup><col width="64" span="4" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"][/TD]
[TD="class: xl64, width: 64"]Sale[/TD]
[TD="class: xl64, width: 64"]Freight[/TD]
[TD="class: xl64, width: 64"]Distance[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl65, align: right"]200[/TD]
[TD="class: xl65, align: right"]35[/TD]
[TD="class: xl65, align: right"]50[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl65, align: right"]300[/TD]
[TD="class: xl65, align: right"]50[/TD]
[TD="class: xl65, align: right"]100[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl65, align: right"]200[/TD]
[TD="class: xl65, align: right"]35[/TD]
[TD="class: xl65, align: right"]50[/TD]
[/TR]
[TR]
[TD="class: xl63"]Answer[/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl66, align: right"]41.43[/TD]
[TD="class: xl66, align: right"]71.43[/TD]
[/TR]
</tbody>[/TABLE]

 

[DanteAmor]

Sorry, but could you show us how you get to those results?

 

[vsr_shimla]

Thanks for your time & Sorry for the trouble,looks like I was not able to explain properly

I have used the normal formula for calculating as under:
Sumproduct(Freight ,Sale)/Sum(Sale) = Weighted Average Freight,
SUMPRODUCT({35;50;35},{200;300;200})/700=Weighted Average Freight

Sumproduct(Sale ,Distance)/Sum(Sale) = Weighted Average Distance
SUMPRODUCT({50;100;50},{200;300;200})/700=Weighted Average Distance


But the problem is when all values are in a single column
rgds

[TABLE="width: 283"]
<colgroup><col><col span="2"></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[DanteAmor]

I'm sorry but I still don't understand how you got 41.43 Forget the formula for a moment and with your words explain how you get that result.

 

[Marcelo Branco]

Maybe...


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][td="bgcolor: #DCE6F1"]

E​

[/td][td="bgcolor: #DCE6F1"]

F​

[/td][td="bgcolor: #DCE6F1"]

G​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

COUNTY​

[/td][td]

Heads​

[/td][td]

Value​

[/td][td][/td][td]

Sales Total​

[/td][td]

Criteria​

[/td][td]

Result​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

A​

[/td][td]

Sale​

[/td][td]

200​

[/td][td][/td][td]

700​

[/td][td]

Freight​

[/td][td]

41,42857​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

A​

[/td][td]

Freight​

[/td][td]

35​

[/td][td][/td][td][/td][td]

Distance​

[/td][td]

71,42857​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

A​

[/td][td]

Distance​

[/td][td]

50​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

B​

[/td][td]

Sale​

[/td][td]

300​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td]

B​

[/td][td]

Freight​

[/td][td]

50​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

7​

[/td][td]

B​

[/td][td]

Distance​

[/td][td]

100​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

8​

[/td][td]

C​

[/td][td]

Sale​

[/td][td]

200​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

9​

[/td][td]

C​

[/td][td]

Freight​

[/td][td]

35​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

10​

[/td][td]

C​

[/td][td]

Distance​

[/td][td]

50​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

Formula in E2
=SUMIF(B:B,"Sale",C:C)

Formula in G2 copied down
=SUMPRODUCT((B$2:B$8="sale")*C$2:C$8,(B3:B9=F2)*C3:C9)/E$2

M.