Evaluate SUMIF error?

[JackDanIce]

Hi,

I have indexed data in K1:K7 and values in L1:L7 (incl. header row)

I'd like to extract unique values from K1:K7 into column G and in column H list SUMIF values. I have the following but it returns the value for the first unique value in K2 on all rows in column H.

Can someone suggest, I think error is in red:

Sub test()

Application.ScreenUpdating = False

With ActiveSheet
    .Range("K1:K7").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=.Cells(1, 7), Unique:=True
    With .Cells(2, 7).Offset(, 1).Resize(x - 1, 1)
       .Value = Evaluate("=IF(" & .Rows & ",SUMIF($K$1:$K$7," & .Offset(, -1).Address & ", $L$1:$L$7)")
    End With
End With

Application.ScreenUpdating = True

End Sub

I have also tried:

.Value = Evaluate("=SUMIF($K$1:$K$," & .Offset(, -1).Address & ", $L$1:$L$7)")

With same incorrect result. I suspect I'm not passing arrays correctly to Evaluate but unclear how.

TIA,
Jack

 

[steve the fish]

I keep this in my personal.xlsm which i use a fair bit for evaluating various formulas with a bit of alteration. The row part does nothing except force excel to use the next .Address. Sort of telling it you are using an array i believe although im no expert in knowing exactly why its required. If you dont use it you often end up, as you did, with the same value being repeated.

Sub CleanTrimEvaluate()

Dim x As Variant
Dim lr As Long

lr = Range("A" & Rows.Count).End(xlUp).Row
 
Set x = Range("A1:A" & lr)
x = Evaluate("=IF(ROW(1:" & lr & "),CLEAN(TRIM(" & x.Address & ")))")
Range("A1:A" & lr) = x

End Sub

 

[RoryA]

As a rule of thumb, if you'd need to array-enter the formula on a worksheet to make it work (or if it only works in a specific location due to implicit intersection) you'll need to coerce Evaluate into returning an array of results.

 

[JackDanIce]

@Steve Yeah I try to keep an Evaluate example around but couldn't get it to work then went off tangent on the solution.

Thank you @RoryA that certainly makes sense, but also when I thought I was doing this in the past, it still gave me errors.

I get the IF part "creates" an array which is filled by the TRUE statement, which is then printed out, but I still seem to struggle with EVALUATE, though seems so much easier and shorter than using loops (where appropriate).

Just need to keep practising until becomes second nature... thanks all for replies on this thread!