ExcelEveryday1
New Member
- Joined
- Jan 7, 2016
- Messages
- 20
Good Morning! I have written a sumif sub that summarizes "value" based off of the criteria in the three columns to the left.
<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?
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?