Sumif

[tiredofit]

On Sheet1, I have the following 10 rows of data with its heading:

Age     Metric
26-30  1
26-30  1
26-30  1
26-30  1
26-30  1
26-30  1
26-30  1
26-30  1
26-30  1
26-30  1

​

I don't understand why after the first iteration in the loop, ModelArray below returns a value of 9 instead of 10?

​

    Dim Rng As Range
    
    Set Rng = Sheet1.Range("$B$2:$B$11")
    
    Dim ModelArray() As Variant
    
    ModelArray() = Sheet1.Cells(1, 1).CurrentRegion.Value
    
    Dim Counter As Integer
    
    For Counter = 2 To 11
    
        ModelArray(Counter, 2) = Application.WorksheetFunction.SumIf(Sheet1.Range("A:A"), "=" & ModelArray(Counter, 1), Rng)
     
    Next Counter ' PUT A BREAK POINT HERE

yet it appears to return the correct result if I changed the range to:

Set Rng = Sheet1.Range("B:B")​

Why is that?

Thanks

 

[oldbrewer]

you only have 10 rows to start with ?

 

[tiredofit]

you only have 10 rows to start with ?

I have 11 rows, of which rows 2 to 11 are the data and row 1 is the heading.

Therefore I thought if I set the range to lookup to be from B2 to B11, it would work but it doesn't.

 

[oldbrewer]

yes - you have 10 rows of data....

 

[RoryA]

Your sum range is offset by one row from your criteria range.

 

[tiredofit]

Your sum range is offset by one row from your criteria range.

Isn't this line saying in plain English:

ModelArray(Counter, 2) = Application.WorksheetFunction.SumIf(Sheet1.Range("A:A"), "=" & ModelArray(Counter, 1), Rng)
​

Sum everything in column A, when it's value is equal to "26-30" and actually sum everything in Rng, where Rng is the range B2 to B11?

If I am correct, then Rng contains the value of 1 (10 times), hence I expected it to return 10, not 9?

 

[RoryA]

Because of the offset, B2 is added if A1 contains the value of interest, then B3 if A2 has it, and so on.

 

[tiredofit]

Thanks