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:

	PER	Market Cap
ABC	15	150
DEF	10	101
XYZ	12	80
Market-cap-weighted average	12.99x
Formula	?

<tbody>
</tbody>

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:

	A	B	C
1	Stock	Per	MktCap
2	ABC	15	150
3	DEF	10	101
4	XYZ	12	80
	Weighted PER	12.98805

<tbody>
</tbody>

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:

	A	B	C
1	Stock	Per	MktCap
2	ABC	15	150
3	DEF	10	101
4	XYZ	12	80
	Weighted PER	12.98805

<tbody>
</tbody>

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(