SUMIFS in VBA

[Zzz999]

Hello. I need help in figuring out on the project I've been working on lately. In excel, I know how to use SUMIFS. I know it's possible to make it work in VBA but I really had no idea how. As I would like to use the formula instead, I really need to do it in macro, because the sheet gets updated every month. This is the formula:

 =SUMIFS(N5:N286,A5:A286,"*Team*", A5:A286, "*Total*")

I above code works if done manually, but it's not really okay because the range is specific and it only ends in 286. I want it to search to the last row of the sheet

I have a sheet (Budget) that gets updated every month. It should find the words "Team" and "Total" in column A and sum up those Amount in column N (same row), and find the words "Grand Total" in column A and its sum will be placed on column N.
Here's the sample table below:

Team	Purchaser	Amount
Team A	Jen	125
	Jen	458
	Jen	569
Team A Total		1152
Team B	Frank	895
	Frank	654
	Frank	774
	Frank	845
	Frank	221
Team B Total		3389
Team C	Mike	415
	Mike	784
Team C Total		1199
Grand Total		0

<tbody>
</tbody>

So the grand total should be 5740 and it will be placed in column N.

Any suggestions will be greatly appreciated. Thank you!

 

[AlphaFrog]

Just reference the entire columns. The function will automatically calculate the used range of the worksheet. It doesn't sum the empty rows beyond your last used row.

=SUMIFS(N:N, A:A,"*Team*", A:A, "*Total*")

Also, I think you could combine the two criteria like this...
=SUMIFS(N:N, A:A,"*Team*Total*")

 

[Zzz999]

Just reference the entire columns. The function will automatically calculate the used range of the worksheet. It doesn't sum the empty rows beyond your last used row.

=SUMIFS(N:N, A:A,"*Team*", A:A, "*Total*")

Also, I think you could combine the two criteria like this...
=SUMIFS(N:N, A:A,"*Team*Total*")

I've also thought about that. But I need to find the "Grand Total" and automatically place the sum on its row in column N

 

[AlphaFrog]

[color=darkblue]Sub[/color] Grand_Total()
    [color=darkblue]Dim[/color] GT [color=darkblue]As[/color] Range
    [color=darkblue]Set[/color] GT = Range("A:A").Find("Grand Total", , , xlWhole, , , [color=darkblue]False[/color])
    [color=darkblue]If[/color] [color=darkblue]Not[/color] GT [color=darkblue]Is[/color] [color=darkblue]Nothing[/color] [color=darkblue]Then[/color]
        Cells(GT.Row, "N").FormulaR1C1 = "=SUMIF(R5C1:R[-1]C1, ""*Team*Total*"", R5C:R[-1]C)"
    [color=darkblue]Else[/color]
        MsgBox "'Grand Total not found in column A. ", vbExclamation, "Grand Taotal Not Found"
    [color=darkblue]End[/color] [color=darkblue]If[/color]
End [color=darkblue]Sub[/color]