Sumifs in previous Excel-Versions

M

matthias.karl

Hi

I have written a custom function that you can use in Excel prior to
2007. It has the same syntax, but can take just 3 different criteria.
But you could add more as needed.

Hope this helps anybody.

Matthias

'******************************************************************************************
'************* SumIf-Function as in XL-2007
***********************************************
'******************************************************************************************
Function SumIfs(SumRng As Range, Crit1Rng As Range, Criteria1 As
String, Crit2Rng As Range, Criteria2 As String, Crit3Rng As Range,
Criteria3 As String) As Double
'The following parameters are necessary
'SumRng as Range: Range to be summed up
'Crit1Rng as Range: Range where the first criteria is
'Criteria1 As String: String with the criteria for Crit1Rng
'Two more pairs with Range and Criteria
'Returns a value as double

Dim c As Range, ColI As Integer, cnt As Integer, C1Cols As Integer,
C2Cols As Integer, C3Cols As Integer
Dim C1 As Boolean, C2 As Boolean, C3 As Boolean, Is1Date As
Boolean, Is2Date As Boolean, Is3Date As Boolean
Dim C1RVal As Variant, C2RVal As Variant, C3RVal As Variant

cnt = 1
C1 = False
C2 = False
C3 = False
Is1Date = False
Is2Date = False
Is3Date = False
ColI = SumRng.Column + 1

For Each c In SumRng
C1Cols = ColI - c.Column
C2Cols = ColI - c.Column
C3Cols = ColI - c.Column

'Criteria1
C1RVal = Crit1Rng.Value(cnt, C1Cols)
If VarType(C1RVal) = 7 Then
Is1Date = True
End If
Select Case True
Case InStr(Left(Criteria1, 2), "<=") > 0
If Is1Date Then
If C1RVal <= Int(Right(Criteria1, Len(Criteria1) - 2))
Then
C1 = True
End If
Else
If C1RVal <= Right(Criteria1, Len(Criteria1) - 2) Then
C1 = True
End If
End If
Case InStr(Left(Criteria1, 2), ">=") > 0
If Is1Date Then
If C1RVal >= Int(Right(Criteria1, Len(Criteria1) - 2))
Then
C1 = True
End If
Else
If C1RVal >= Right(Criteria1, Len(Criteria1) - 2) Then
C1 = True
End If
End If
Case InStr(Left(Criteria1, 2), "<>") > 0
If Is1Date Then
If C1RVal <> Int(Right(Criteria1, Len(Criteria1) - 2))
Then
C1 = True
End If
Else
If C1RVal <> Right(Criteria1, Len(Criteria1) - 2) Then
C1 = True
End If
End If
Case InStr(Left(Criteria1, 1), "<") > 0
If Is1Date Then
If C1RVal < Int(Right(Criteria1, Len(Criteria1) - 1))
Then
C1 = True
End If
Else
If C1RVal < Right(Criteria1, Len(Criteria1) - 1) Then
C1 = True
End If
End If
Case InStr(Left(Criteria1, 1), ">") > 0
If Is1Date Then
If C1RVal > Int(Right(Criteria1, Len(Criteria1) - 1))
Then
C1 = True
End If
Else
If C1RVal > Right(Criteria1, Len(Criteria1) - 1) Then
C1 = True
End If
End If
Case InStr(Left(Criteria1, 1), "=") > 0
If Is1Date Then
If C1RVal = Int(Right(Criteria1, Len(Criteria1) - 1))
Then
C1 = True
End If
Else
If C1RVal = Right(Criteria1, Len(Criteria1) - 1) Then
C1 = True
End If
End If
Case Else
If C1RVal = Criteria1 Then
C1 = True
End If
End Select


'Criteria2
C2RVal = Crit2Rng.Value(cnt, C2Cols)
If VarType(C2RVal) = 7 Then
Is2Date = True
End If
Select Case True
Case InStr(Left(Criteria2, 2), "<=") > 0
If Is2Date Then
If C2RVal <= Int(Right(Criteria2, Len(Criteria2) - 2))
Then
C2 = True
End If
Else
If C2RVal <= Right(Criteria2, Len(Criteria2) - 2) Then
C2 = True
End If
End If
Case InStr(Left(Criteria2, 2), ">=") > 0
If Is2Date Then
If C2RVal >= Int(Right(Criteria2, Len(Criteria2) - 2))
Then
C2 = True
End If
Else
If C2RVal >= Right(Criteria2, Len(Criteria2) - 2) Then
C2 = True
End If
End If
Case InStr(Left(Criteria2, 2), "<>") > 0
If Is2Date Then
If C2RVal <> Int(Right(Criteria2, Len(Criteria2) - 2))
Then
C2 = True
End If
Else
If C2RVal <> Right(Criteria2, Len(Criteria2) - 2) Then
C2 = True
End If
End If
Case InStr(Left(Criteria2, 1), "<") > 0
If Is2Date Then
If C2RVal < Int(Right(Criteria2, Len(Criteria2) - 1))
Then
C2 = True
End If
Else
If C2RVal < Right(Criteria2, Len(Criteria2) - 1) Then
C2 = True
End If
End If
Case InStr(Left(Criteria2, 1), ">") > 0
If Is2Date Then
If C2RVal > Int(Right(Criteria2, Len(Criteria2) - 1))
Then
C2 = True
End If
Else
If C2RVal > Right(Criteria2, Len(Criteria2) - 1) Then
C2 = True
End If
End If
Case InStr(Left(Criteria2, 1), "=") > 0
If Is2Date Then
If C2RVal = Int(Right(Criteria2, Len(Criteria2) - 1))
Then
C2 = True
End If
Else
If C2RVal = Right(Criteria2, Len(Criteria2) - 1) Then
C2 = True
End If
End If
Case Else
If C2RVal = Criteria2 Then
C2 = True
End If
End Select
Is2Date = False

'Criteria3
C3RVal = Crit3Rng.Value(cnt, C3Cols)
If VarType(C3RVal) = 7 Then
Is3Date = True
End If
Select Case True
Case InStr(Left(Criteria3, 2), "<=") > 0
If Is3Date Then
If C3RVal <= Int(Right(Criteria3, Len(Criteria3) - 2))
Then
C3 = True
End If
Else
If C3RVal <= Right(Criteria3, Len(Criteria3) - 2) Then
C3 = True
End If
End If
Case InStr(Left(Criteria3, 2), ">=") > 0
If Is3Date Then
If C3RVal >= Int(Right(Criteria3, Len(Criteria3) - 2))
Then
C3 = True
End If
Else
If C3RVal >= Right(Criteria3, Len(Criteria3) - 2) Then
C3 = True
End If
End If
Case InStr(Left(Criteria3, 2), "<>") > 0
If Is3Date Then
If C3RVal <> Int(Right(Criteria3, Len(Criteria3) - 2))
Then
C3 = True
End If
Else
If C3RVal <> Right(Criteria3, Len(Criteria3) - 2) Then
C3 = True
End If
End If
Case InStr(Left(Criteria3, 1), "<") > 0
If Is3Date Then
If C3RVal < Int(Right(Criteria3, Len(Criteria3) - 1))
Then
C3 = True
End If
Else
If C3RVal < Right(Criteria3, Len(Criteria3) - 1) Then
C3 = True
End If
End If
Case InStr(Left(Criteria3, 1), ">") > 0
If Is3Date Then
If C3RVal > Int(Right(Criteria3, Len(Criteria3) - 1))
Then
C3 = True
End If
Else
If C3RVal > Right(Criteria3, Len(Criteria3) - 1) Then
C3 = True
End If
End If
Case InStr(Left(Criteria3, 1), "=") > 0
If Is3Date Then
If C3RVal = Int(Right(Criteria3, Len(Criteria3) - 1))
Then
C3 = True
End If
Else
If C3RVal = Right(Criteria3, Len(Criteria3) - 1) Then
C3 = True
End If
End If
Case Else
If C3RVal = Criteria3 Then
C3 = True
End If
End Select
Is3Date = False

If C1 = True And C2 = True And C3 = True Then 'If Crit1, Crit2
and Crit3 are true, then sum the cell
SumIfs = SumIfs + c.Value
End If
C1 = False
C2 = False
C3 = False

cnt = cnt + 1
Next

End Function
 
T

T. Valko

The Excel 2007 SUMIFS function can be "emulated" in previous versions of
Excel using the SUMPRODUCT function.
 
J

JE McGimpsey

I haven't looked this over at all extensively, but these lines

C1RVal = Crit1Rng.Value(cnt, C1Cols)
...
C2RVal = Crit2Rng.Value(cnt, C2Cols)
....
C3RVal = Crit3Rng.Value(cnt, C3Cols)

will generate Compile errors since Value is a scalar property.

Just on a flyer, I tried changing them to, e.g.,

C3RVal = Crit3Rng(cnt, C3Cols).Value

but got a result of 0 as an (incorrect) result, so I gave up.
 
J

JE McGimpsey

True - though it's handy in mixed environments to have an add-in for
previous XL versions that implements the new function(s) in VBA. That
allows one to develop workbooks that can be passed between XL07/8 and
the previous versions without worrying about compatibility.

The one that I'm using takes the SUMIFS arguments and builds a
SUMPRODUCT() argument to execute and return the value.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VBA Coding Help for Beginner 0
VBA Export to PDF 0
Making a macro only work on certain files 2
Word macro that can call cells out of Excel for a find and replace 0
type mismatch error in macro in excel 0
VBA-Table Word 2019 - Move a selected text, from a cell to the adjacent cell to its right, by pressing the ENTER key 0
Multi Select List Not Working 1
Closing PDF file from Excel VBA (Adobe Reader, Adobe Acrobat, Browser) 0

Top