vba worksheet function for sumifs where criteria range in sheet1 and criteria in sheet 2

0
votes

I am trying to a vba code with worksheet function sumifs. I have two sheets, sheet 1 contains criteria range and sheet 2 contains criteria.

Sheet 1:

Group   Year    Cutomer Amount
A   2012    Gold        1000
B   2013    Platinum    1250
C   2012    Gold        1500
B   2012    Platinum    1750
C   2013    Gold        2000
A   2013    Platinum    2250
C   2012    Gold        2500
A   2013    Platinum    2750
B   2012    Gold        3000


Sheet 2:

                2012    2013
A   Gold        
A   Platinum        
B   Gold        
B   Platinum        
C   Gold        
C   Platinum

My code is as follows:

Sub SUMIFSTWOSHEETS()

Dim i As Variant
Dim condition As Range

For i = 2 To 7

sheet2.Cells(i, 3) = WorksheetFunction.sumifs(sheet1.Range("d2:d10"), sheet1.Range("a2:a10"), sheet2.Cells(i, 1), sheet1.Range("c2:c10"), sheet2.Cells(i, 2), sheet1.Range("B2:B10"), sheet2.Range("c2"))
sheet2.Cells(i, 4) = WorksheetFunction.sumifs(sheet1.Range("d2:d10"), sheet1.Range("a2:a10"), sheet2.Cells(i, 1), sheet1.Range("c2:c10"), sheet2.Cells(i, 2), sheet1.Range("B2:B10"), sheet2.Range("d2"))

Next i

End Sub

With the above code, vba is running but it gives only zero as answer.

My answer is as follows:

        2012    2013
A   Gold        0   0
A   Platinum    0   0
B   Gold        0   0
B   Platinum    0   0
C   Gold        0   0
C   Platinum    0   0

Can somebody help me out?.

3
excelvba

3 Answers

1
votes

If I understand your tab placement correctly, try replacing sheet2.Range("c2") with sheet2.Range("c1") and sheet2.Range("d2") with sheet2.Range("d1"). It should give you the following output in Sheet2:

                2012    2013
A   Gold        1000    0
A   Platinum    0       5000
B   Gold        3000    0
B   Platinum    1750    1250
C   Gold        4000    2000
C   Platinum    0       0

As a side note, I am not sure why you need i to be Variant: a simple Long should be OK?

1
votes

The values that you are summing should be the last parameter. Thus,

sheet2.Cells(i, 3) = WorksheetFunction.sumifs(sheet1.Range("a2:a10"), sheet2.Cells(i, 1), sheet1.Range("c2:c10"), sheet2.Cells(i, 2), sheet1.Range("B2:B10"), sheet2.Range("c2"), sheet1.Range("d2:d10"))

My question is why do you need a macro and not just put the formula in Excel.

0
votes
Sub sumifs_worksheet()


Dim i As Variant
Dim condition As Range

For i = 2 To 8
'Sheet2.Cells(i, 3).Value = WorksheetFunction.SumIfs(Sheet1.Range("d2:d10"), Sheet1.Range("a2:a10"), Sheet2.Cells(i, 1), Sheet1.Range("c2:c10"), Sheet2.Cells(i, 2), Sheet1.Range("B2:B10"), Sheet2.Range("c2"))

Sheet2.Cells(i, 3).Value = WorksheetFunction.SumIfs(Sheet1.Range("d2:d8"), Sheet1.Range("a2:a8"), Sheet2.Cells(i, 1), Sheet1.Range("c2:c8"), Sheet2.Cells(i, 2), Sheet1.Range("B2:B8"), Sheet2.Cells(1, 3))
Sheet2.Cells(i, 4).Value = WorksheetFunction.SumIfs(Sheet1.Range("d2:d8"), Sheet1.Range("a2:a8"), Sheet2.Cells(i, 1), Sheet1.Range("c2:c8"), Sheet2.Cells(i, 2), Sheet1.Range("B2:B8"), Sheet2.Cells(1, 4))

Next i

End Sub