VB and VBA Users Source Code: Excel: Performing a SUMIF between a min and max value
[
Home
|
Contents
|
Search
|
Reply
| Previous | Next ]
VB/VBA Source Code
Excel: Performing a SUMIF between a min and max value
By:
Andrew Baker
Email (spam proof):
Email the originator of this post
Date:
Tuesday, January 17, 2006
Hits:
3077
Category:
VBA (Visual Basic for Applications)
Article:
The routine below performs a SUMIF for values between a specified min and max value. 'Purpose : Performs a range lookup, similiar to VLOOKUP and SUMIF but allows you to specify a min and max value. 'Inputs : minValue The minimum value to sum. ' maxValue The maximum value to sum. ' tableArray The range in which to search the first column ' for values which are between the min and max values. ' columnIndex The index of the column to sum any matching rows. 'Outputs : Returns the sum of any matching value. 'Author : Andrew Baker 'Date : 28/04/2001 'Example : 'Below is an example of the sheet formulea used to sum the "Value" column where the '"Start Date" and "End Date" columns are between the "Value to Lookup" column: 'Start Date: End Date: Value: Value to Lookup: Sum of value Lookup: '=TODAY() =TODAY()+1 100 =TODAY() =SUMIFBETWEEN(D4,D4,$A$4:$C$8,3) '=B4 =A5+1 101 =D4+1 =SUMIFBETWEEN(D5,D5,$A$4:$C$8,3) '=B5 =A6+1 102 =D5+1 =SUMIFBETWEEN(D6,D6,$A$4:$C$8,3) '=B6 =A7+1 103 =D6+1 =SUMIFBETWEEN(D7,D7,$A$4:$C$8,3) '=B7 =A8+1 104 =D7+1 =SUMIFBETWEEN(D8,D8,$A$4:$C$8,3) Function SUMIFBETWEEN(minValue As Variant, maxValue As Variant, tableArray As Excel.Range, columnIndex As Long) As Variant Dim oCell As Excel.Range Dim result As Variant Application.Volatile True For Each oCell In tableArray.Rows On Error GoTo ErrFailed If IsEmpty(oCell.Value) = False Then 'Can error if types are not the same, but code will just 'catch and ignore the error. If oCell.Value >= minValue And oCell.Value <= maxValue Then 'Value between range result = result + tableArray.Columns(oCell.Row, columnIndex) End If End If ErrFailed: Next SUMIFBETWEEN = result End Function
Terms and Conditions
Support this site
Download a trial version of the Excel Workbook Rebuilder
