All,

I have the following code.

I have read up on SUMIF fucntions and seen all the options with pivot
tables, sorting, and using arrays. However I would like to find a
simple way to add another criteria to the code that I already have.
For example I would only like the code to 'run' and extract the values
of there is specific information in another column (B). For example
there is another column in the worksheet labelled 'yes/no'.The value
will be either Y or N. I would like to run the code to say that if the
value is Y then it uses the data in cloumn K and S and adds it to the
sum, and if its N then it just ignores it.

Any sugestions.

Thanks in advance for your help,


Regards,

Joseph Crabtree

With Sheets("Data")
LastRow = Sheets("Data").Range("K" & Rows.Count).End(xlUp).Row
Set CodeRange = .Range("K2:K" & LastRow)
Set SumRange = .Range("S2:S" & LastRow)

End With

Sheets("data").Activate
Range("K1", "K" & LastRow).Select
Selection.AdvancedFilter Action:=xlFilterInPlace, Unique:=True
Selection.Copy Sheets("output").Range("A1")
ActiveSheet.ShowAllData

Set CriteriaRange = Sheets("Output").Range("A2")
For r = 2 To Sheets("Output").Range("A2").End(xlDown).Row
Total = WorksheetFunction.SumIf(CodeRange, CriteriaRange,
SumRange)
CriteriaRange.Offset(0, 1) = Total
Set CriteriaRange = CriteriaRange.Offset(1, 0)
Next