Excel VBA: how to sum only values ​​that exceed a specific value?

Question

I spent all day at work trying to find a solution to my problem. Most of my googleing brought me to this site, so I decided to refuse and ask for help.

I have an Excel file containing stock portfolio positions. I need to figure out how to write a macro that can tell me right away if those positions that exceed 5% of the portfolio value together exceed 40% of the portfolio value.

I have some basic understanding of VBA and I know how to choose the right column, but I cannot figure out how to sum only those positions that exceed 5% (0.05). I tried different If expressions, but I just can't figure out what is right.

Can someone please show me the way? I just need a macro to check my column and then print in MsgBox whether the positions (> = 0.05) will exceed 40% (0.40).

Thank you very much!

Example column:

Weights 0.032 0.067 0.103 . . . 0.02 

Answer 1

If I understand your question correctly, I think what you want below.

 Sub GetThreshold() Dim myRng As Range Dim pos As Double Set myRng = ThisWorkbook.Sheets("Sheet1").Range("A2:A100") For Each mycell In myRng If mycell.Value >= 0.05 Then pos = pos + mycell.Value End If Next If pos > 0.4 Then MsgBox "Position exceeds the threshold of 40%", vbCritical, "Position Break" Else MsgBox "Position within the threshold of 40%", vbInformation, "Position Break" End If End Sub 

Hope this helps.

Answer 2

To compose a Doug Glancy comment, you must use SUMIF for this. Assuming your portfolio weights are in the range of A2: A200, this will give you what you need:

=SUMIF(A2:A200,">=0.05")