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

 

[Fluff]

Try

       .Value = Evaluate("=IF({1},SUMIF($K$1:$K$8," & .Offset(, -1).Address & ", $L$1:$L$8))")

Also what is x?

I got an error with the .Rows, also you are missing a closing bracket.

 

[Fluff]

As it happens you don't need the If, you can just use

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

 

[JackDanIce]

Morning Fluff, x was used for last used row but because I just wanted it to work, I hard-coded range - unfortunately your suggest doesn't work

Set up (ranges have now changed):

Brand new sheet
In K1:"Header", K2:K5: "A", "B", "C", "D", K6:87: "A", "B", "C", K9:K10: "A", "B", K11: "A"
L1: "Value" L2:L11 "=ROW()", values then hard coded

Running code below returns:

G1: "Header", G2:G5: "A", "B", "C", "D"
H2:H5 all have 28, which is the sum of "A" values only in column L only

Sub test()

Dim x As Long

Application.ScreenUpdating = False

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

Application.ScreenUpdating = True

End Sub

 

[steve the fish]

Running that code for me produces:

[TABLE="width: 128"]
<colgroup><col style="width:48pt" width="64" span="2"> </colgroup><tbody>[TR]
[TD="width: 64"]Header[/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD="align: right"]28[/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD]c[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD]d[/TD]
[TD="align: right"]5[/TD]
[/TR]
</tbody>[/TABLE]

 

[JackDanIce]

Thanks Steve, my screen is showing with the code I posted in #4 - I repeated the steps I said above in a new file (.xlsx, insert module, copy code in, then set sheet up as described and I still get:

Header
A 28
B 28
C 28
D 28

Have I misunderstood the code or doing something incorrect? Calculation mode is set to Automatic, I can't figure out what else to check.

 

[steve the fish]

No matter what i do i cant replicate that. I just keep getting what i posted.

 

[JackDanIce]

It's weird, it happened at home yesterday when I posted the Q and again at work, replicating the problem.

Given you and Fluff *are* getting correct results, I must be doing something wrong, however, I don't understand what or where!

Again, brand new sheet, this time using column A for header values (A, B, C, D, A, B, C, A, B, A) and column B "=ROW()" hardcoded, with output in column D, I still get A, B, C, D all with SUMIF values of 28 in column E.

Sub test()


Dim x As Long


Application.ScreenUpdating = False


With ActiveSheet
    .Range("A1:A11").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=.Cells(1, 4), Unique:=True
    x = .Cells(.Rows.Count, 4).End(xlUp).Row
    With .Cells(2, 4).Offset(, 1).Resize(x - 1, 1)
       .Value = Evaluate("SUMIF($A$1:$A$11," & .Offset(, -1).Address & ", $B$1:$B11)")
    End With
End With


Application.ScreenUpdating = True


End Sub

It may be a pain, but would you mind stating your exact steps and code used if you try this in a brand new sheet/workbook?

 

[steve the fish]

In K1:K11

[TABLE="width: 64"]
<colgroup><col style="width:48pt" width="64"> </colgroup><tbody>[TR]
[TD="width: 64"]Header[/TD]
[/TR]
[TR]
[TD]a[/TD]
[/TR]
[TR]
[TD]b[/TD]
[/TR]
[TR]
[TD]c[/TD]
[/TR]
[TR]
[TD]d[/TD]
[/TR]
[TR]
[TD]a[/TD]
[/TR]
[TR]
[TD]b[/TD]
[/TR]
[TR]
[TD]c[/TD]
[/TR]
[TR]
[TD]a[/TD]
[/TR]
[TR]
[TD]b[/TD]
[/TR]
[TR]
[TD]a[/TD]
[/TR]
</tbody>[/TABLE]

In L1

value

In L2:L11

=row() copied down

The macro:

Sub aatest()

Dim x As Long

Application.ScreenUpdating = False

With ActiveSheet
    .Range("K1:K11").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=.Cells(1, 7), Unique:=True
    x = .Cells(.Rows.Count, 7).End(xlUp).Row
    With .Cells(2, 7).Offset(, 1).Resize(x - 1, 1)
       '.Value = Evaluate("SUMIF($K$1:$K$11," & .Offset(, -1).Address & ", $L$1:$L11)")
       .Value = Evaluate("=IF({1},SUMIF($K$1:$K$11," & .Offset(, -1).Address & ", $L$1:$L$11))")
    End With
End With

Application.ScreenUpdating = True

End Sub

Either evaluate line works for me and produces as i said earlier.

<colgroup><col width="64"></colgroup><tbody>
[TD="width: 64"][/TD]

</tbody>

<colgroup><col width="64"></colgroup><tbody>
</tbody>

 

[JackDanIce]

Hi Steve, this does work, I didn't use "=IF({1}" in my code as I thought from earlier, it wasn't needed (in this case)

Still figuring out Evaluate - I know it needs array sized inputs and the output array needs to be of same size

In this instance either

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

works or

.Value = Evaluate("=IF({1},SUMIF($K$1:$K$11," & .Offset(, -1).Address & ", $L$1:$L$11))")

Missing code in blue, vs code posted to #8

Thank you @Fluff and @steve the fish helping to resolve