Sumif - For Each problem (several column comparison)
 

Hi,

I've got a problem. I need to compare two columns in one
sheet(1) with two columns in another sheet(2). If the
cells in several rows (column A&B) in one sheet(1) exactly
match a row in the other sheet(2) (column A&B), I like the
values in column C to be summarized in the second sheet(2).

(This is a part of a bigger problem and a PIVOT solution
is not applicable)

Sheet 1 (data) Sheet2 (result)
ColA ColB ColC ColA ColB ColC
Row1 AA BA 15 Row1 AA BA 25
Row2 AA BA 10 Row2 AB BA 5
Row3 AB BA 5

I'll tried a For each - sumif script, but did not succeed.
Se below:

Sub I_UpdateHours()
Dim rng As Range, rng1 As Range, cell As Range
Dim res As Variant

With Worksheets("Works")
Set rng = .Range(.Cells(2, 1), .Cells(Rows.Count, 2).End
(xlUp))
End With

With Worksheets("Database")
Set rng1 = .Range(.Cells(2, 33), .Cells(Rows.Count,
34).End(xlUp))
End With

For Each cell In rng
If Application.SumIf(rng1, cell.Value, rng1.Offset(0,
1)) = 0 Then
Else

cell.Offset(0, 2).Value = _
Application.SumIf(rng1, cell.Value, rng1.Offset(0, -
25))
End If
Next

End Sub

Appreciate any help to get this to work!

Thanks!
Mats W

Sumif - For Each problem (several column comparison)
 

Hi
try using Sumproduct. See:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

--
Regards
Frank Kabel
Frankfurt, Germany

"Mats W" schrieb im Newsbeitrag
...
Hi,

I've got a problem. I need to compare two columns in one
sheet(1) with two columns in another sheet(2). If the
cells in several rows (column A&B) in one sheet(1) exactly
match a row in the other sheet(2) (column A&B), I like the
values in column C to be summarized in the second sheet(2).

(This is a part of a bigger problem and a PIVOT solution
is not applicable)

Sheet 1 (data) Sheet2 (result)
ColA ColB ColC ColA ColB ColC
Row1 AA BA 15 Row1 AA BA 25
Row2 AA BA 10 Row2 AB BA 5
Row3 AB BA 5

I'll tried a For each - sumif script, but did not succeed.
Se below:

Sub I_UpdateHours()
Dim rng As Range, rng1 As Range, cell As Range
Dim res As Variant

With Worksheets("Works")
Set rng = .Range(.Cells(2, 1), .Cells(Rows.Count, 2).End
(xlUp))
End With

With Worksheets("Database")
Set rng1 = .Range(.Cells(2, 33), .Cells(Rows.Count,
34).End(xlUp))
End With

For Each cell In rng
If Application.SumIf(rng1, cell.Value, rng1.Offset(0,
1)) = 0 Then
Else

cell.Offset(0, 2).Value = _
Application.SumIf(rng1, cell.Value, rng1.Offset(0, -
25))
End If
Next

End Sub

Appreciate any help to get this to work!

Thanks!
Mats W