Sumproduct Multiple criteria same column

vsr_shimla

New Member
Joined
Sep 23, 2019
Messages
6
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
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Welcome to the forum!

According to your example, what values ​​should be added and what is the expected result?
 
Upvote 0
What's the weight (factor)?

Excel 2016 (Windows) 64 bit
A
B
C
D
1
COUNTYHeadsValueWeight
2
ASale
200​
?
3
AFreight
35​
?
4
ADistance
50​
?
5
BSale
300​
?
6
BFreight
50​
?
7
BDistance
100​
?
8
CSale
200​
?
9
CFreight
35​
?
10
CDistance
50​
?
Sheet: Sheet1
 
Upvote 0
As pointed out by DanteAmor it would help to know the expected result.
But Maybe?
Excel Workbook
ABCDEF
1COUNTYHeadsValue
2ASale200
3AFreight35
4ADistance50Wt. Average
5BSale300136.6666667
6BFreight50
7BDistance100
8CSale200
9CFreight35
10CDistance50
Sheet
 
Upvote 0
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
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]A[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]B[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]C[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]D[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]1[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]COUNTY[/COLOR][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Heads[/COLOR][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Value[/COLOR][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Weight[/COLOR]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]2[/COLOR]​
ASale
200​
?
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]3[/COLOR]​
AFreight
35​
?
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]4[/COLOR]​
ADistance
50​
?
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]5[/COLOR]​
BSale
300​
?
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]6[/COLOR]​
BFreight
50​
?
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]7[/COLOR]​
BDistance
100​
?
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]8[/COLOR]​
CSale
200​
?
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]9[/COLOR]​
CFreight
35​
?
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]10[/COLOR]​
CDistance
50​
?

<tbody>
</tbody>
Sheet: Sheet1

<tbody>
</tbody>

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

SaleFreightDistance
2003550
30050100
2003550
Answer 41.4371.43

<colgroup><col width="64" span="4" style="width:48pt"> </colgroup><tbody>
</tbody>
 
Upvote 0
Sorry, but could you show us how you get to those results?
 
Upvote 0
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


<colgroup><col><col span="2"></colgroup><tbody>
</tbody>
 
Upvote 0
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.
 
Upvote 0
Maybe...


A
B
C
D
E
F
G
1
COUNTY​
Heads​
Value​
Sales Total​
Criteria​
Result​
2
A​
Sale​
200​
700​
Freight​
41,42857​
3
A​
Freight​
35​
Distance​
71,42857​
4
A​
Distance​
50​
5
B​
Sale​
300​
6
B​
Freight​
50​
7
B​
Distance​
100​
8
C​
Sale​
200​
9
C​
Freight​
35​
10
C​
Distance​
50​

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.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top