VBA SUMIF with multiple criteria

[ExcelEveryday1]

Good Morning! I have written a sumif sub that summarizes "value" based off of the criteria in the three columns to the left.

[TABLE="width: 292"]
<tbody>[TR]
[TD]Year[/TD]
[TD]Contract type[/TD]
[TD]Price Range[/TD]
[TD]Value[/TD]
[/TR]
[TR]
[TD]Year1[/TD]
[TD]A[/TD]
[TD]Twenty >[/TD]
[TD="align: right"]19[/TD]
[/TR]
[TR]
[TD]Year2[/TD]
[TD]B[/TD]
[TD]Fourty +[/TD]
[TD="align: right"]45[/TD]
[/TR]
[TR]
[TD]Year2[/TD]
[TD]C[/TD]
[TD]Twenty >[/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD]Year2[/TD]
[TD]D[/TD]
[TD]Fourty +[/TD]
[TD="align: right"]50[/TD]
[/TR]
</tbody><colgroup><col><col span="3"></colgroup>[/TABLE]


Unfortunately, 2 years, with 4 contract types and 2 price ranges leaves me with 16 combinations of variables to sumif, and I will probably have to do this with more variables in the future. I have sumIfed the total as seen below for all 16 combinations using 4 "for to" loops

For x = FirstRow To LastRow
If Cells(x, 2) = Cells(3, 6) And Cells(x, 3) = Cells(2, 7) And Cells(x, 4) = Cells(2, 8) Then
Ce = 1 + Ce
ElseIf Cells(x, 2) = Cells(3, 6) And Cells(x, 3) = Cells(3, 7) And Cells(x, 4) = Cells(2, 8) Then
Cf = 1 + Cf
ElseIf Cells(x, 2) = Cells(3, 6) And Cells(x, 3) = Cells(4, 7) And Cells(x, 4) = Cells(2, 8) Then
Cg = 1 + Cg
ElseIf Cells(x, 2) = Cells(3, 6) And Cells(x, 3) = Cells(5, 7) And Cells(x, 4) = Cells(2, 8) Then
Ch = 1 + Ch
End If
Next x


etc....


There's got to be a simpler way to do this. Any ideas?

 

[Jonmo1]

Which version of Excel are you using?
In XL2007+, we have the SUMIFS function, perfectly suited for that task.

 

[ExcelEveryday1]

Which version of Excel are you using?
In XL2007+, we have the SUMIFS function, perfectly suited for that task.

Lol Thanks, I found the sumifs func

 

[Jonmo1]

You're welcome.