VBA SUM Function

[joecrabtree]

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

 

[Gary''s Student]

Consider using SUMPRODUCT in place of SUMIF

 

[joel]

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]

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.

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

 

[joel]

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

 

[Dave Peterson]

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]

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) & ")"

 

[joecrabtree]

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) & ")"

Thanks for all your help. Ive now got this working, and understand how
it works. However I havent been able to get an output, i.e. displayed
sumproduct value. For example how would I be able to get this to
display the outputs an output worksheet?

Thanks again,

Joe Crabtree

 

[Dave Peterson]

Got it working means that
Total = Application.Evaluate(MyFormula)
actually evaluated to the correct number?

If yes, then maybe something like after this line:

Total = Application.Evaluate(MyFormula)
..Range("X" & r).Value = total

 

[joel]

You can also put the sumproduct formula in the worksheet

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

to (added equal sign in front of forumal)
MyFormula = "=Sumproduct(" & _
"--(" & CodeRange.Address(external:=true) & "=""" _
& .Range("K" & r).Value & """)," & _
"--(" & CriteriaRange.Address(external:=true) & "=""Y"")," & _
SumRange.Address(external:=true) & ")"
Range("A1").formula = Myformula

 

[joecrabtree]

You can also put the sumproduct formula in the worksheet

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

to (added equal sign in front of forumal)
 MyFormula = "=Sumproduct(" & _
      "--(" & CodeRange.Address(external:=true) & "=""" _
                & .Range("K" & r).Value & """),"  & _
      "--(" & CriteriaRange.Address(external:=true) & "=""Y"")," & _
      SumRange.Address(external:=true) & ")"
 Range("A1").formula = Myformula

All,

Thankyou both for your input. I now have another question based on
this code.Using this code I have a workbook with a worksheet in it
called 'data'. The format is shown below:

Column Letter B K S
1 YES/NO CODE VALUE
2 Y ABC 10
3 Y ABC 2
4 N ABB 44
5 N ABC 23

 

[joecrabtree]

All,

Thankyou both for your input. I now have another question based on
this code.Using this code I have a workbook with a worksheet in it
called 'data'. The format is shown below:

Column Letter   B       K       S
1       YES/NO  CODE    VALUE
2       Y       ABC     10
3       Y       ABC     2
4       N       ABB     44
5       N       ABC     23

Apologies- that was posted in error!

Thankyou both for your input. I now have another question based on
this code.Using this code I have a workbook with a worksheet in it
called 'data'. The format is shown below:

B K S
1 YES/NO CODE VALUE
2 Y ABC 10
3 Y ABC 2
4 N ABB 44
5 N ABC 23

I also have another sheet called 'output' . What I would like to do is
sum all of the code values based on the Y/N criteria. For example in
this case. The output would be:

B K S
1 YES/NO CODE VALUE
2 Y ABC 12

If the data changed to:

B K S
1 YES/NO CODE VALUE
2 Y ABC 10
3 Y ABC 2
4 Y ABB 44
5 N ABC 23

Then the output would be:


B K S
1 YES/NO CODE VALUE
2 Y ABC 12
3 Y ABB 44

and so on. The data is in columns B (Y/N), K (CODE), and S(Value).

Do you have any idea how I can make this work?

Thanks in advance for all your help,

Regards

Joseph Crabtree

 

[joel]

I'm not sure exactly what you have in mind. There are a few different
methods people ask for in similar situations.

1) If you want to create the table in the output and don't havve all the
codes. then what you need to do is to get unique codes. On a worksheet you
can go to the menu - Data - Advance filter. Select the original data table
to the code colun. Then select copy to a new location. Specify the new
location, and also select Unique. You can record a macro will performing
this operation.

2) Once you have the unique values in the outp[ut table then you can add the
Y or N in the column B and the formula in column S.

----------------------------------------------------------------------------
Now if you already have the table and want to condense the table yo would
want to replace the formula with values. Then combine rows that have the
same code. You would need to sort the table to make sure items with the same
code are adjacent. Then it is a simple macro to combine the rows and add the
values.

I will help with anything you want. But I want to make sure I'm writing
the macro you want.

 

[joecrabtree]

I'm not sure exactly what you have in mind.  There are a few different
methods people ask for in similar situations.

1) If you want to create the table in the output and don't havve all the
codes.  then what you need to do is to get unique codes.  On a worksheet you
can go to the menu - Data - Advance filter.  Select the original data table
to the code colun.  Then select copy to a new location.  Specify the new
location, and also select Unique.  You can record a macro will performing
this operation.

2) Once you have the unique values in the outp[ut table then you can add the
Y or N in the column B and the formula in column S.

----------------------------------------------------------------------------
Now if you already have the table and want to condense the table yo would
want to replace the formula with values.  Then combine rows that have the
same code.  You would need to sort the table to make sure items with the same
code are adjacent.  Then it is a simple macro to combine the rows and add the
values.

I will help with anything you want.   But I want to make sure I'm writing
the macro you want.

Apologies- that was posted in error!

Thankyou both for your input. I now have another question based on
this code.Using this code I have a workbook with a worksheet in it
called 'data'. The format is shown below:

       B               K                 S
1  YES/NO  CODE    VALUE
2  Y                ABC    10
3  Y                ABC    2
4  N                ABB            44
5  N                 ABC   23

I also have another sheet called 'output' . What I would like to do is
sum all of the code values based on the Y/N criteria. For example in
this case. The output would be:

       B               K                 S
1  YES/NO  CODE    VALUE
2  Y                ABC    12

If the data changed to:

       B               K                 S
1  YES/NO  CODE    VALUE
2  Y                ABC    10
3  Y                ABC    2
4  Y                ABB            44
5  N                 ABC   23

Then the output would be:

       B               K                 S
1  YES/NO  CODE    VALUE
2  Y                ABC    12
3  Y                ABB    44

and so on. The data is in columns B (Y/N), K (CODE), and S(Value).

Do you have any idea how I can make this work?

Thanks in advance for all your help,

Joseph Crabtree

My idea was to do the whole thing using VBA, i.e completed automated.
The ideal solution would be the first one that you suggested:

1) If you want to create the table in the output and don't havve all
the
codes. then what you need to do is to get unique codes. On a
worksheet you
can go to the menu - Data - Advance filter. Select the original data
table
to the code colun. Then select copy to a new location. Specify the
new
location, and also select Unique. You can record a macro will
performing
this operation.

2) Once you have the unique values in the outp[ut table then you can
add the
Y or N in the column B and the formula in column S.

Any help you could give would be much appreciate.

Regards

Joe Crabtree

 

[joel]

Dave and I have shown you the SUMPRODUCT and you keep on going back to the
SUMIF which won't work.

I'm not sure exactly what you have in mind. There are a few different
methods people ask for in similar situations.

1) If you want to create the table in the output and don't havve all the
codes. then what you need to do is to get unique codes. On a worksheet you
can go to the menu - Data - Advance filter. Select the original data table
to the code colun. Then select copy to a new location. Specify the new
location, and also select Unique. You can record a macro will performing
this operation.

2) Once you have the unique values in the outp[ut table then you can add the
Y or N in the column B and the formula in column S.

----------------------------------------------------------------------------
Now if you already have the table and want to condense the table yo would
want to replace the formula with values. Then combine rows that have the
same code. You would need to sort the table to make sure items with the same
code are adjacent. Then it is a simple macro to combine the rows and add the
values.

I will help with anything you want. But I want to make sure I'm writing
the macro you want.

On Mar 25, 2:29 pm, joel <[email protected]> wrote:

You can also put the sumproduct formula in the worksheet

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

to (added equal sign in front of forumal)
MyFormula = "=Sumproduct(" & _
"--(" & CodeRange.Address(external:=true) & "=""" _
& .Range("K" & r).Value & """)," & _
"--(" & CriteriaRange.Address(external:=true) & "=""Y"")," & _
SumRange.Address(external:=true) & ")"
Range("A1").formula = Myformula

:
Got it working means that
Total = Application.Evaluate(MyFormula)
actually evaluated to the correct number?

If yes, then maybe something like after this line:

Total = Application.Evaluate(MyFormula)
..Range("X" & r).Value = total

joecrabtree wrote:

Thanks for all your help. Ive now got this working, and understand how
it works. However I havent been able to get an output, i.e. displayed
sumproduct value. For example how would I be able to get this to
display the outputs an output worksheet?

Thanks again,

Joe Crabtree

Dave Peterson

Thankyou both for your input. I now have another question based on
this code.Using this code I have a workbook with a worksheet in it
called 'data'. The format is shown below:

Column Letter B K S
1 YES/NO CODE VALUE
2 Y ABC 10
3 Y ABC 2
4 N ABB 44
5 N ABC 23

Apologies- that was posted in error!

Thankyou both for your input. I now have another question based on
this code.Using this code I have a workbook with a worksheet in it
called 'data'. The format is shown below:

B K S
1 YES/NO CODE VALUE
2 Y ABC 10
3 Y ABC 2
4 N ABB 44
5 N ABC 23

I also have another sheet called 'output' . What I would like to do is
sum all of the code values based on the Y/N criteria. For example in
this case. The output would be:

B K S
1 YES/NO CODE VALUE
2 Y ABC 12

If the data changed to:

B K S
1 YES/NO CODE VALUE
2 Y ABC 10
3 Y ABC 2
4 Y ABB 44
5 N ABC 23

Then the output would be:

B K S
1 YES/NO CODE VALUE
2 Y ABC 12
3 Y ABB 44

and so on. The data is in columns B (Y/N), K (CODE), and S(Value).

Do you have any idea how I can make this work?

Thanks in advance for all your help,

Joseph Crabtree

My idea was to do the whole thing using VBA, i.e completed automated.
The ideal solution would be the first one that you suggested:

1) If you want to create the table in the output and don't havve all
the
codes. then what you need to do is to get unique codes. On a
worksheet you
can go to the menu - Data - Advance filter. Select the original data
table
to the code colun. Then select copy to a new location. Specify the
new
location, and also select Unique. You can record a macro will
performing
this operation.

2) Once you have the unique values in the outp[ut table then you can
add the
Y or N in the column B and the formula in column S.

Any help you could give would be much appreciate.

Regards

Joe Crabtree