Application.WorksheetFunction.SumIf: a range as a criteria

[Nelson78]

Hello everybody.

I'm struggling with a variant of sumif.

Below a classic example:

Result = Application.WorksheetFunction.SumIf(Range("A:A"), Range("C1").Value, Range("B:B"))

Now, suppose the criteria is not univocal.

I mean: the criteria is not in C1, but has to correspond in one of the value included in a range.

For example, in the range rng

dim lr as long
lr = Cells(Rows.Count, "C").End(xlUp).Row
set rng as range
rng = Range("C2:C" & lr)

How can I figure this out?

Thank's in advance.

 

[Nelson78]

Hello everybody.

I'm struggling with a variant of sumif.

Below a classic example:

Result = Application.WorksheetFunction.SumIf(Range("A:A"), Range("C1").Value, Range("B:B"))

Now, suppose the criteria is not univocal.

I mean: the criteria is not in C1, but has to correspond in one of the value included in a range.

For example, in the range rng

dim lr as long
lr = Cells(Rows.Count, "C").End(xlUp).Row
set rng as range
rng = Range("C2:C" & lr)

How can I figure this out?

Thank's in advance.

This way is impossible:

Result = Application.WorksheetFunction.SumIf(Range("A:A"), Range("C2").Value, Range("B:B")) +  _
 Application.WorksheetFunction.SumIf(Range("A:A"), Range("C3").Value, Range("B:B")) [COLOR="#FF0000"][B]+ ... + _
 [/B][/COLOR] Application.WorksheetFunction.SumIf(Range("A:A"), Range("C[COLOR="#FF0000"][B]?[/B][/COLOR]").Value, Range("B:B"))

 

[mikerickson]

You could loop.

Dim i as Long, lr as Long, mySum as Long

lr = Cells(Rows.Count, "C").End(xlUp).Row

For i = 1 to lr
    mySum = mySum = Application.WorksheetFunction.SumIf(Range("A:A"), Cells(i, 3).Value, Range("B:B"))
Next i

 

[Nelson78]

You could loop.

Dim i as Long, lr as Long, mySum as Long

lr = Cells(Rows.Count, "C").End(xlUp).Row

For i = 1 to lr
    mySum = mySum = Application.WorksheetFunction.SumIf(Range("A:A"), Cells(i, 3).Value, Range("B:B"))
Next i

It works fine. Thank you.