SUMPRODUCT/SUM with criteria

[XcelLearner]

I have a portfolio with hundred of stocks, but for simplicity, let's say I have three stocks, with their respective PERs and market caps:

[TABLE="width: 318"]
<tbody>[TR]
[TD][/TD]
[TD]PER[/TD]
[TD]Market Cap[/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]150[/TD]
[/TR]
[TR]
[TD]DEF[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]101[/TD]
[/TR]
[TR]
[TD]XYZ[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]80[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Market-cap-weighted average[/TD]
[TD="align: right"]12.99x[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Formula[/TD]
[TD]?[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


I would like to take a market-cap-weighted PER for the entire portfolio, and the formula would be "SUMPRODUCT(B2:B5,C2:C5)/SUM(C2:C5)".

Suddenly, I want to have a market-cap-weighted PER for a portfolio of stocks with their market caps larger than 100. The result should be 12.99. However, I don't know how to revise the formula with condition of market cap >100.

Could you please help? Thank you very much.

 

[XcelLearner]

I figured out it myself. The formula should be:
=SUMPRODUCT(--(C2:C4>100),B2:B4,C2:C4)/SUMIF(C2:C4,">100")

Thanks to this thread:
https://www.mrexcel.com/forum/excel...t-function-only-values-greater-than-zero.html

Hopefully that will help anyone with similar problems.

 

[Indystick]

I setup my table like this:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Stock[/TD]
[TD]Per[/TD]
[TD]MktCap[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]ABC[/TD]
[TD]15[/TD]
[TD]150[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]DEF[/TD]
[TD]10[/TD]
[TD]101[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]XYZ[/TD]
[TD]12[/TD]
[TD]80[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Weighted PER[/TD]
[TD]12.98805[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Here is the formula I came up with:

{ =SUM(IF(C2:C4>100,B2:B4)*IF(C2:C4>100,C2:C4))/SUMIF(C2:C4,">100") }

 

[Nine Zero]

try this out

=SUMPRODUCT(B2:B4,--(C2:C4>100),C2:C4)/SUMIF(C2:C4,">100",C2:C4)

EDIT: woops i see people beat me to it lol should have refreshed

 

[XcelLearner]

I setup my table like this:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Stock[/TD]
[TD]Per[/TD]
[TD]MktCap[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]ABC[/TD]
[TD]15[/TD]
[TD]150[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]DEF[/TD]
[TD]10[/TD]
[TD]101[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]XYZ[/TD]
[TD]12[/TD]
[TD]80[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Weighted PER[/TD]
[TD]12.98805[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Here is the formula I came up with:

{ =SUM(IF(C2:C4>100,B2:B4)*IF(C2:C4>100,C2:C4))/SUMIF(C2:C4,">100") }

Hi, how should I revise the formula if I want to have several criterion, such as:
market cap >100 and
PER > 10

Thanks.

 

[Indystick]

You’d need to incorporate and AND with the IFs and change SUMIF to SUMIFs. I will try and get the exact syntax for you soon.

 

[Aladin Akyurek]

Hi, how should I revise the formula if I want to have several criterion, such as:
market cap >100 and
PER > 10

Thanks.

An array-processing formula with SUMPRODUCT...

=SUMPRODUCT(--(cap>100),--(per>10),B2:B4,C2:C4)/SUMIFS(cap,per,">10",cap,">100")

or an array-processing formula with SUM and IF...

=SUM(IF(cap>100,IF(per>10,cap*per)))/SUM(IF(cap>10,IF(per>10,cap)))


The latter requires confirming with control+shift+enter, not just enter:




=SUMPRODUCT(