Sumif - Non Contiguous Columns - Syntax please
This will work for your example and assumes you already have Sum formulas in
your Total row.
Sub TotalIf()
Dim TblRng As String 'for SUMIF formula
Dim CritRng As String 'for SUMIF formula
Dim SumRng As String 'for SUMIF formula
Dim MyRng As Range 'where formula goes
Dim FrmlRow As Long 'row for formula
Dim FrmlCol As Integer 'column for formula
TblRng = "$A$8:$F$13" 'where data is
FrmlRow = 2 'first formula row
FrmlCol = 2 'first formula column
For FrmlCol = 2 To 6
If FrmlCol = 4 Then FrmlCol = 6 'skips col 4 & 5
CritRng = Range("A" & FrmlRow).Address
SumRng = Range(Cells(8, FrmlCol), Cells(13, FrmlCol)).Address
Set MyRng = Cells(FrmlRow, FrmlCol)
MyRng.Formula = "=Sumif(" & TblRng & "," & CritRng & "," & SumRng &
")"
FrmlRow = FrmlRow + 1 'go to next row
For FrmlRow = 3 To 4
CritRng = Range("A" & FrmlRow).Address
Set MyRng = Cells(FrmlRow, FrmlCol)
MyRng.Formula = "=Sumif(" & TblRng & "," & CritRng & "," & SumRng &
")"
Next
FrmlRow = 2 'go back to first row
Next
End Sub
Mike F
"u473" wrote in message
oups.com...
Simplified Sample below :
Criteria in A2 thru A4
Columns to be summed B, C, F in Rows 2 to 4. Data Rows 8 to 13
Col A Col B Col C Col F
A 6 9 10
B 10 2
C 7 12 3
-------------------------------------
Total 23 23 13
-------------------------------------
A 2 4 6
A 5
A 4 4
B 10 2
C 4 3
C 7 8
Found few articles on this subject or in my Bibles.
How do I smartly build my For Each's
and generate my Total Row ?
Help Appreciated,
Wayne,
|