VBA Sumifs Sytanx Help

[denzo36]

Hi guys, new ish to VBA so would appreciate some help on the syntax for this, can't seem to find what I'm looking for elsewhere.

I would like to create a sumifs and countif formula in VBA that calculates a sum of premium and count of policy number.

I would like to create a 'Summary' sheet that basically looks at another sheet which has my data and does a sumifs based on 2 criteria and a countif based on one criteria (so I'm referencing a different sheet to the sheet my formulas will be in). Below is the formula in Excel:

=SUMIFS('All Key Risks'!D:D,'All Key Risks'!I:I,A2&"*")

where the name of the sheet I'm referencing is 'All Key Risks' and taking the sum range of column D with the criteria range being in column I and the criteria being the value in cell A2 of active sheet and adding a wildcard character.

I'm struggling to get the syntax right in my VBA code. Currently I have the below where B2 is the cell I want the formula populated in the 'Summary' sheet.

Range("B2").Value = application.WorksheetFunction.SumIfs(worksheets(1).range("D:D"),worksheets(1).range("I:I"),A2&"*"))

The problem I have is the correct way to reference the sheet name in my formula. The reason is used 'Worksheets(1) is because the data I'm summing from will always be on Sheet 1 so I'm trying to reference the range D:D and I:I on that sheet.

Anyone have a solution?

 

[Fluff]

How about

Range("B2").Value = application.SumIfs(worksheets(1).range("D:D"),worksheets(1).range("I:I"),range("A2")&"*"))

 

[denzo36]

Ahh perfect thanks so much, I nearly had it!

 

[Fluff]

You're welcome & thanks for the feedback

 

[denzo36]

Hi Fluff - one more thing if possible. I've used your formula which works great but when I try and fill the formula down for all cells in the range its filling down the values of cells B2 instead of adjusting the sumifs formula for each cell column A (i.e. last argument in my Sumifs formula needs to move as I copy the formula down to look at A1, A2, A3 etc to end of that range)

Your previous tip: Range("B2").Value = Application.WorksheetFunction.SumIfs(Worksheets(1).Range("D:D"), Worksheets(1).Range("I:I"), Range("A2"))

Want to fill this formula to fill down for all cells in the range. My attempt: Range("B2").AutoFill Range("B2:B" & Range("A" & Rows.Count).End(xlUp).Row)

Any ideas?

 

[Fluff]

Because you are inserting the result of the formula, not the formula itself, you cannot fill down.
You either need to put the formula in & then convert to values, or loop through the range calculating for each row

 

[denzo36]

Ok perfect, thanks again for your help!

 

[Fluff]

You're welcome & thanks for the feedback