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.

Year	Contract type	Price Range	Value
Year1	A	Twenty >	19
Year2	B	Fourty +	45
Year2	C	Twenty >	15
Year2	D	Fourty +	50

<tbody>
</tbody><colgroup><col><col span="3"></colgroup>

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.