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

"joecrabtree" wrote:

On Mar 26, 11:19 am, joel wrote:
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" wrote:
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

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