Usage of SUMIF formula in the VBA code

I am trying to use Excel "SUMIF" function in VBA. I was able to do it without
problem by inserting the formula into the cell ( Range(Cells(21, 2), Cells(21
+ RowNumber, 2 + ColumnNumber)).Formula = "=sumIf($a$1:$A$16, $a21,
b$1:b$16)").

But, when I tried to do the same thing using "WorksheetFunction." (
Range(Cells(35, 2), Cells(35 + RowNumber, 2 + ColumnNumber)) =
WorksheetFunction.SumIf(Range(Cells(1, 1), Cells(16, 1)), Range(Cells(21 +
RowNumber, 1), Cells(21 + RowNumber, 1)), Range(Cells(1, 2 + ColumnNumber),
Cells(16, 2 + ColumnNumber)))
) I do not get the same results. It seems to me they are equivalent and
should provide the same results. But, the results says I am wrong and I was
wondering what I am missing here? Thanks.

Here is the entire code:

Sub test()
For RowNumber = 0 To 2
For ColumnNumber = 0 To 3
Range(Cells(21, 2), Cells(21 + RowNumber, 2 + ColumnNumber)).Formula
= "=sumIf($a$1:$A$16, $a21, b$1:b$16)"
Range(Cells(35, 2), Cells(35 + RowNumber, 2 + ColumnNumber)) =
WorksheetFunction.SumIf(Range(Cells(1, 1), Cells(16, 1)), Range(Cells(21 +
RowNumber, 1), Cells(21 + RowNumber, 1)), Range(Cells(1, 2 + ColumnNumber),
Cells(16, 2 + ColumnNumber)))
Next
Next
End Sub

Try

Cells(35 + RowNumber, 2 + ColumnNumber) = _
WorksheetFunction.SumIf(Range(Cells(1, 1), Cells(16, 1)), Cells(21 + _
RowNumber, 1), Range(Cells(1, 2 + ColumnNumber), Cells(16, 2 + ColumnNumber)))

HTH

"GreenInIowa" wrote:

> I am trying to use Excel "SUMIF" function in VBA. I was able to do it without
> problem by inserting the formula into the cell ( Range(Cells(21, 2), Cells(21
> + RowNumber, 2 + ColumnNumber)).Formula = "=sumIf($a$1:$A$16, $a21,
> b$1:b$16)").
>
> But, when I tried to do the same thing using "WorksheetFunction." (
> Range(Cells(35, 2), Cells(35 + RowNumber, 2 + ColumnNumber)) =
> WorksheetFunction.SumIf(Range(Cells(1, 1), Cells(16, 1)), Range(Cells(21 +
> RowNumber, 1), Cells(21 + RowNumber, 1)), Range(Cells(1, 2 + ColumnNumber),
> Cells(16, 2 + ColumnNumber)))
> ) I do not get the same results. It seems to me they are equivalent and
> should provide the same results. But, the results says I am wrong and I was
> wondering what I am missing here? Thanks.
>
> Here is the entire code:
>
> Sub test()
> For RowNumber = 0 To 2
> For ColumnNumber = 0 To 3
> Range(Cells(21, 2), Cells(21 + RowNumber, 2 + ColumnNumber)).Formula
> = "=sumIf($a$1:$A$16, $a21, b$1:b$16)"
> Range(Cells(35, 2), Cells(35 + RowNumber, 2 + ColumnNumber)) =
> WorksheetFunction.SumIf(Range(Cells(1, 1), Cells(16, 1)), Range(Cells(21 +
> RowNumber, 1), Cells(21 + RowNumber, 1)), Range(Cells(1, 2 + ColumnNumber),
> Cells(16, 2 + ColumnNumber)))
> Next
> Next
> End Sub

Thanks, Toppers.

I could not believe this was this simple!

"Toppers" wrote:

> Try
>
> Cells(35 + RowNumber, 2 + ColumnNumber) = _
> WorksheetFunction.SumIf(Range(Cells(1, 1), Cells(16, 1)), Cells(21 + _
> RowNumber, 1), Range(Cells(1, 2 + ColumnNumber), Cells(16, 2 + ColumnNumber)))
>
> HTH
>
> "GreenInIowa" wrote:
>
> > I am trying to use Excel "SUMIF" function in VBA. I was able to do it without
> > problem by inserting the formula into the cell ( Range(Cells(21, 2), Cells(21
> > + RowNumber, 2 + ColumnNumber)).Formula = "=sumIf($a$1:$A$16, $a21,
> > b$1:b$16)").
> >
> > But, when I tried to do the same thing using "WorksheetFunction." (
> > Range(Cells(35, 2), Cells(35 + RowNumber, 2 + ColumnNumber)) =
> > WorksheetFunction.SumIf(Range(Cells(1, 1), Cells(16, 1)), Range(Cells(21 +
> > RowNumber, 1), Cells(21 + RowNumber, 1)), Range(Cells(1, 2 + ColumnNumber),
> > Cells(16, 2 + ColumnNumber)))
> > ) I do not get the same results. It seems to me they are equivalent and
> > should provide the same results. But, the results says I am wrong and I was
> > wondering what I am missing here? Thanks.
> >
> > Here is the entire code:
> >
> > Sub test()
> > For RowNumber = 0 To 2
> > For ColumnNumber = 0 To 3
> > Range(Cells(21, 2), Cells(21 + RowNumber, 2 + ColumnNumber)).Formula
> > = "=sumIf($a$1:$A$16, $a21, b$1:b$16)"
> > Range(Cells(35, 2), Cells(35 + RowNumber, 2 + ColumnNumber)) =
> > WorksheetFunction.SumIf(Range(Cells(1, 1), Cells(16, 1)), Range(Cells(21 +
> > RowNumber, 1), Cells(21 + RowNumber, 1)), Range(Cells(1, 2 + ColumnNumber),
> > Cells(16, 2 + ColumnNumber)))
> > Next
> > Next
> > End Sub