Excel Custom Function: Sum Top/Bottom X Numbers In 1 Column or Row
Category: [Excel] Demo Available
Excel Custom Function: Sum Top/Bottom X Numbers In 1 Column or Row
Got any Excel/VBA Questions? Excel Help.
Sums the top/bottom N numbers in 1 column/row range. NO TEXT ALLOWED
The Code
Function SumTopBottomN(rRange As Range, N As Long, Optional bBottomN As Boolean) As Single
Dim strAddress As String
'''''''''''''''''''''''''''''''''''''''''
'Written by Ozgrid.com'''''''''''''''''''
'Sums the top/bottom x numbers in a single column/row list
'''''''''''''''''''''''''''''''''''''''''
On Error Resume Next
strAddress = rRange.Address
If bBottomN = False Then
SumTopBottomN = Evaluate("=SUMPRODUCT((" _
& strAddress & ">=LARGE(" & strAddress & "," & X & "))*(" & strAddress & "))")
Else
SumTopBottomN = Evaluate("=SUMPRODUCT((" _
& strAddress & "<=SMALL(" & strAddress & "," & X & "))*(" & strAddress & "))")
End If
End Function
To use this UDF push Alt+F11 and go Insert>Module and paste in the code. Push Alt+Q and save. The Function will appear under "User Defined" in the Paste Function dialog box (Shift+F3). Use the Function in any cell as shown below.
=SumTopBottomN($A$2:$A$100,10) For top 10
OR
=SumTopBottomN($A$2:$A$100,10,TRUE) For bottom 10
See also:
| Index to Excel VBA Code |
See also Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions.
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.