On Mar 26, 8:17*am, joecrabtree wrote:
On Mar 25, 2:29*pm, joel 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

"Dave Peterson" wrote:
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:

<<snipped

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

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