AdvancedFilter using a SUMIF as a criteria

Question

I am trying to use AdvancedFilter in VBA to create an export of a list. My issue is not with the code, which works, but with the criteria I am trying to use. I want to filter on values that are a SUMIF of the RAW dataset I am using. So, if on the RAW data =sumif(m:m,a:a,a2)<2, then exclude from the list. How would I put this in a my criteria (Range a1:a2)? Is the only solution to make a new column in my raw dataset that contains the sumifs formula and then base a criteria off of that?

Code for reference

FilterData()

Sheets("RAW").Range("A1:n2000").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:= _
    Sheets("Criteria").Range("a1:a2"), CopyToRange:=Sheets("EXPORT").Range("A1:n1"), Unique:=True
Columns.AutoFit
Cells.WrapText = False
  End Sub

exSnake exSnake · Accepted Answer · 2017-08-27T09:30:59

You can set the formula in another cell with a blank cell upon the criteria, maybe in the top of the sheet, referencing only the first data list row without locking cell:

example, you are advance filtering

range("A10:M2010")

in

range("A2")

put your criteria as:

=sumif($M$10:$M$2010,$A$10:$A10$2010,A10)<2

and leave range("A1") blank,

then use the advance filter selecting the

list range as $A$10:$M$2010

then the

criteria range as 'SheetName'!$A$1:$A$2 (Blank cell, and formula)

Excel will do the same thing as creating another column on the right of the range to filter then will filter it.

Try this with filter and copy in another place.

Here is the sample (with the Italian version of Excel):

AdvancedFilter using a SUMIF as a criteria

1 Answers