title: vba worksheet function for sumifs where criteria range in sheet1 and criteria in sheet 2
body: <p>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.</p>
  
  <p>Sheet 1:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>My code is as follows:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>With the above code, vba is running but it gives only zero as answer.</p>
  
  <p>My answer is as follows:</p>
  
  <pre><code>        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
  </code></pre>
  
  <p>Can somebody help me out?.</p>