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

Consider using SUMPRODUCT in place of SUMIF
--
Gary''s Student - gsnu200840


"joecrabtree" wrote:

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

I have a question with the code below. It looks like the following statement
is creating a new workbook. Is that what you want?

Selection.Copy Sheets("output").Range("A1")

When you do a copy and don't use AFTER or BEFORE the new worksheet gets
added into a new workbook.

You have a lot of postings in the last couple of days. I prefer to respond
to you latest code. If you want to have a Yes/No column let me know which
column the Y and N are located. I agree with Gary the way you are writing
the code that Sumproduct will work.

I find sometimes it is better to just write the code (in your case Total) by
moving down one row at a time then to use a worksheet function.

"joecrabtree" wrote:

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

On Mar 24, 2:46*pm, joel wrote:
I have a question with the code below. *It looks like the following statement
is creating a new workbook. *Is that what you want?

Selection.Copy Sheets("output").Range("A1")

When you do a copy and don't use AFTER or BEFORE the new worksheet gets
added into a new workbook.

You have a lot of postings in the last couple of days. *I prefer to respond
to you latest code. *If you want to have a Yes/No column let me know which
column the Y and N are located. *I agree with Gary the way you are writing
the code that Sumproduct will work.

I find sometimes it is better to just write the code (in your case Total) by
moving down one row at a time then to use a worksheet function.

"joecrabtree" wrote:
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

Apologies for all the postings. The Y/N column is in column B.

In essence all I wanted to do was to be able to expand the criteria of
SUMIF to include another column, i.e Y/N.

How can i modify it to use sum product?"

Thanks

Joe

I'm not an expert in using Sumproduct from VBA code. This is the way I've
gotten it toook work in the past. Before you modify this code run it the way
it is and look at the formula in the message box so you understand how it
works.

MYformula produces this line of text. I use evalute to run this statement
like it was on the worksheet. Notice NO equal sign before SUMPRODUCT

"Sumproduct(--($K$2:$K$8="b"),--($B$2:$B$8="Y"),$S$2:$S$8)"



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)
Set CriteriaRange = .Range("B2:B" & LastRow)

For r = 2 To LastRow
MyFormula = "Sumproduct(" & _
"--(" & CodeRange.Address & "=""" & .Range("K" & r).Value & """),"
& _
"--(" & CriteriaRange.Address & "=""Y"")," & _
SumRange.Address & ")"

msgbox(Myformula)
Total = Application.Evaluate(MyFormula)

Next r

End With

"joecrabtree" wrote:

On Mar 24, 2:46 pm, joel wrote:
I have a question with the code below. It looks like the following statement
is creating a new workbook. Is that what you want?

Selection.Copy Sheets("output").Range("A1")

When you do a copy and don't use AFTER or BEFORE the new worksheet gets
added into a new workbook.

You have a lot of postings in the last couple of days. I prefer to respond
to you latest code. If you want to have a Yes/No column let me know which
column the Y and N are located. I agree with Gary the way you are writing
the code that Sumproduct will work.

I find sometimes it is better to just write the code (in your case Total) by
moving down one row at a time then to use a worksheet function.

"joecrabtree" wrote:
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

Apologies for all the postings. The Y/N column is in column B.

In essence all I wanted to do was to be able to expand the criteria of
SUMIF to include another column, i.e Y/N.

How can i modify it to use sum product?"

Thanks

Joe

I didn't look at the code very closely, but I think that the data is spread over
two sheets. That means that the OP needs to go back to the original code to set
those ranges.

And this formula:
=Sumproduct(--($K$2:$K$8="b"),--($B$2:$B$8="Y"),$S$2:$S$8)
needs to look more like:
=Sumproduct(--(Data!$K$2:$K$8="B"),--(Data!$B$2:$B$8="Y"),data!$S$2:$S$8)

Application.evaluate() will use the cells on the activesheet for any unqualified
address.

You could activate Data, then create the formula or even use:
total = worksheets("data").evaluate(myformula)

Or you could qualify those ranges:

MyFormula = "Sumproduct(" & _
"--(" & CodeRange.Address & "=""" & .Range("K" & r).Value & """)," & _
"--(" & CriteriaRange.Address & "=""Y"")," & _
SumRange.Address & ")"

would look more like:

MyFormula = "Sumproduct(" & _
"--(" & CodeRange.Address(external:=true) & "=""" _
& .Range("K" & r).Value & """)," & _
"--(" & CriteriaRange.Address(external:=true) & "=""Y"")," & _
SumRange.Address(external:=true) & ")"

Since you used worksheets("Data").range("K" & r).value, it won't change.

Untested, uncompiled!





joel wrote:

I'm not an expert in using Sumproduct from VBA code. This is the way I've
gotten it toook work in the past. Before you modify this code run it the way
it is and look at the formula in the message box so you understand how it
works.

MYformula produces this line of text. I use evalute to run this statement
like it was on the worksheet. Notice NO equal sign before SUMPRODUCT

"Sumproduct(--($K$2:$K$8="b"),--($B$2:$B$8="Y"),$S$2:$S$8)"

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)
Set CriteriaRange = .Range("B2:B" & LastRow)

For r = 2 To LastRow
MyFormula = "Sumproduct(" & _
"--(" & CodeRange.Address & "=""" & .Range("K" & r).Value & """),"
& _
"--(" & CriteriaRange.Address & "=""Y"")," & _
SumRange.Address & ")"

msgbox(Myformula)
Total = Application.Evaluate(MyFormula)

Next r

End With

"joecrabtree" wrote:

On Mar 24, 2:46 pm, joel wrote:
I have a question with the code below. It looks like the following statement
is creating a new workbook. Is that what you want?

Selection.Copy Sheets("output").Range("A1")

When you do a copy and don't use AFTER or BEFORE the new worksheet gets
added into a new workbook.

You have a lot of postings in the last couple of days. I prefer to respond
to you latest code. If you want to have a Yes/No column let me know which
column the Y and N are located. I agree with Gary the way you are writing
the code that Sumproduct will work.

I find sometimes it is better to just write the code (in your case Total) by
moving down one row at a time then to use a worksheet function.

"joecrabtree" wrote:
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

Apologies for all the postings. The Y/N column is in column B.

In essence all I wanted to do was to be able to expand the criteria of
SUMIF to include another column, i.e Y/N.

How can i modify it to use sum product?"

Thanks

Joe


--

Dave Peterson

Dave: Thanks. I was thinking about multiple worksheets after I posted. The
external = true is the best solution. I don't like selecting worksheets.


MyFormula = "Sumproduct(" & _
"--(" & CodeRange.Address(external:=true) & "=""" _
& .Range("K" & r).Value & """)," & _
"--(" & CriteriaRange.Address(external:=true) & "=""Y"")," & _
SumRange.Address(external:=true) & ")"

"Dave Peterson" wrote:

I didn't look at the code very closely, but I think that the data is spread over
two sheets. That means that the OP needs to go back to the original code to set
those ranges.

And this formula:
=Sumproduct(--($K$2:$K$8="b"),--($B$2:$B$8="Y"),$S$2:$S$8)
needs to look more like:
=Sumproduct(--(Data!$K$2:$K$8="B"),--(Data!$B$2:$B$8="Y"),data!$S$2:$S$8)

Application.evaluate() will use the cells on the activesheet for any unqualified
address.

You could activate Data, then create the formula or even use:
total = worksheets("data").evaluate(myformula)

Or you could qualify those ranges:

MyFormula = "Sumproduct(" & _
"--(" & CodeRange.Address & "=""" & .Range("K" & r).Value & """)," & _
"--(" & CriteriaRange.Address & "=""Y"")," & _
SumRange.Address & ")"

would look more like:

MyFormula = "Sumproduct(" & _
"--(" & CodeRange.Address(external:=true) & "=""" _
& .Range("K" & r).Value & """)," & _
"--(" & CriteriaRange.Address(external:=true) & "=""Y"")," & _
SumRange.Address(external:=true) & ")"

Since you used worksheets("Data").range("K" & r).value, it won't change.

Untested, uncompiled!





joel wrote:

I'm not an expert in using Sumproduct from VBA code. This is the way I've
gotten it toook work in the past. Before you modify this code run it the way
it is and look at the formula in the message box so you understand how it
works.

MYformula produces this line of text. I use evalute to run this statement
like it was on the worksheet. Notice NO equal sign before SUMPRODUCT

"Sumproduct(--($K$2:$K$8="b"),--($B$2:$B$8="Y"),$S$2:$S$8)"

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)
Set CriteriaRange = .Range("B2:B" & LastRow)

For r = 2 To LastRow
MyFormula = "Sumproduct(" & _
"--(" & CodeRange.Address & "=""" & .Range("K" & r).Value & """),"
& _
"--(" & CriteriaRange.Address & "=""Y"")," & _
SumRange.Address & ")"

msgbox(Myformula)
Total = Application.Evaluate(MyFormula)

Next r

End With

"joecrabtree" wrote:

On Mar 24, 2:46 pm, joel wrote:
I have a question with the code below. It looks like the following statement
is creating a new workbook. Is that what you want?

Selection.Copy Sheets("output").Range("A1")

When you do a copy and don't use AFTER or BEFORE the new worksheet gets
added into a new workbook.

You have a lot of postings in the last couple of days. I prefer to respond
to you latest code. If you want to have a Yes/No column let me know which
column the Y and N are located. I agree with Gary the way you are writing
the code that Sumproduct will work.

I find sometimes it is better to just write the code (in your case Total) by
moving down one row at a time then to use a worksheet function.

"joecrabtree" wrote:
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

Apologies for all the postings. The Y/N column is in column B.

In essence all I wanted to do was to be able to expand the criteria of
SUMIF to include another column, i.e Y/N.

How can i modify it to use sum product?"

Thanks

Joe


--

Dave Peterson