I have an Excel 2003 spreadsheet with the following cell formula:

SUMPRODUCT((Data!$N$5:$N$10000=$S11)*(Base!$L$5:$L $10000="COST")*(Base!$I$5:$I$10000))

The column of $S11 is about 500 rows.

I have an aversion to cell formulas and would like to carry out this
computation with a Macro. Is there an existing macro routine that does this
same type of calculation?

use a UDF (User Defined Function)

eg


Option Explicit

Function MySumProduct()

MySumProduct =
Evaluate("=SUMPRODUCT((Data!$N$5:$N$10000=$S11)*(B ase!$L$5:$L$10000=""COST"")*(Base!$I$5:$I$10000))" )

End Function

in your sheet, select a cell and type
=MySumProduct()





"MavenDog" wrote:


I have an Excel 2003 spreadsheet with the following cell formula:

SUMPRODUCT((Data!$N$5:$N$10000=$S11)*(Base!$L$5:$L $10000="COST")*(Base!$I$5:$I$10000))

The column of $S11 is about 500 rows.

I have an aversion to cell formulas and would like to carry out this
computation with a Macro. Is there an existing macro routine that does this
same type of calculation?

Patrick,

Thank you so much. I tried it, and it worked fine. I had never heard of
the 'evaluate' method. I appreciate your taking the time to help me. I
would never have gotten past the need for the double quote even if I had
known about 'evaluate'.

Do you have a favorite reference book that includes more detailed
programming information than the Visual Basic manual that came with my Office
2003?

Jim (aka Mavendog)

"Patrick Molloy" wrote:

use a UDF (User Defined Function)

eg


Option Explicit

Function MySumProduct()

MySumProduct =
Evaluate("=SUMPRODUCT((Data!$N$5:$N$10000=$S11)*(B ase!$L$5:$L$10000=""COST"")*(Base!$I$5:$I$10000))" )

End Function

in your sheet, select a cell and type
=MySumProduct()





"MavenDog" wrote:


I have an Excel 2003 spreadsheet with the following cell formula:

SUMPRODUCT((Data!$N$5:$N$10000=$S11)*(Base!$L$5:$L $10000="COST")*(Base!$I$5:$I$10000))

The column of $S11 is about 500 rows.

I have an aversion to cell formulas and would like to carry out this
computation with a Macro. Is there an existing macro routine that does this
same type of calculation?

SUMPRODUCT((Data!$N$5:$N$10000=$S11)*...
Is there an existing macro routine that does this..

Hi. Just to give another idea:

Sub Demo()
Dim x
With WorksheetFunction
x = .SumIfs([Base!I5:I20], [Data!N5:N20], [S11], [Base!L5:L20], "Cost")
End With
End Sub

Although 'Sumproduct' is the most popular, sometimes I find the above
reads a little easier (in some cases).

= = = = =
Dana DeLouis


On 2/10/2010 8:51 PM, TexPop wrote:

Patrick,

Thank you so much. I tried it, and it worked fine. I had never heard of
the 'evaluate' method. I appreciate your taking the time to help me. I
would never have gotten past the need for the double quote even if I had
known about 'evaluate'.

Do you have a favorite reference book that includes more detailed
programming information than the Visual Basic manual that came with my Office
2003?

Jim (aka Mavendog)

"Patrick Molloy" wrote:

use a UDF (User Defined Function)

eg


Option Explicit

Function MySumProduct()

MySumProduct =
Evaluate("=SUMPRODUCT((Data!$N$5:$N$10000=$S11)*(B ase!$L$5:$L$10000=""COST"")*(Base!$I$5:$I$10000))" )

End Function

in your sheet, select a cell and type
=MySumProduct()





"MavenDog" wrote:


I have an Excel 2003 spreadsheet with the following cell formula:

SUMPRODUCT((Data!$N$5:$N$10000=$S11)*(Base!$L$5:$L $10000="COST")*(Base!$I$5:$I$10000))

The column of $S11 is about 500 rows.

I have an aversion to cell formulas and would like to carry out this
computation with a Macro. Is there an existing macro routine that does this
same type of calculation?



--
= = = = = = =
HTH :)
Dana DeLouis

Thank you Dana for your suggestion. I appreciate your response. I am
beginning to think I should move to a relational data base system considering
the size of my data bases. It takes forever to compute in Excel.

Jim

"Dana DeLouis" wrote:

SUMPRODUCT((Data!$N$5:$N$10000=$S11)*...
Is there an existing macro routine that does this..

Hi. Just to give another idea:

Sub Demo()
Dim x
With WorksheetFunction
x = .SumIfs([Base!I5:I20], [Data!N5:N20], [S11], [Base!L5:L20], "Cost")
End With
End Sub

Although 'Sumproduct' is the most popular, sometimes I find the above
reads a little easier (in some cases).

= = = = =
Dana DeLouis


On 2/10/2010 8:51 PM, TexPop wrote:

Patrick,

Thank you so much. I tried it, and it worked fine. I had never heard of
the 'evaluate' method. I appreciate your taking the time to help me. I
would never have gotten past the need for the double quote even if I had
known about 'evaluate'.

Do you have a favorite reference book that includes more detailed
programming information than the Visual Basic manual that came with my Office
2003?

Jim (aka Mavendog)

"Patrick Molloy" wrote:

use a UDF (User Defined Function)

eg


Option Explicit

Function MySumProduct()

MySumProduct =
Evaluate("=SUMPRODUCT((Data!$N$5:$N$10000=$S11)*(B ase!$L$5:$L$10000=""COST"")*(Base!$I$5:$I$10000))" )

End Function

in your sheet, select a cell and type
=MySumProduct()





"MavenDog" wrote:


I have an Excel 2003 spreadsheet with the following cell formula:

SUMPRODUCT((Data!$N$5:$N$10000=$S11)*(Base!$L$5:$L $10000="COST")*(Base!$I$5:$I$10000))

The column of $S11 is about 500 rows.

I have an aversion to cell formulas and would like to carry out this
computation with a Macro. Is there an existing macro routine that does this
same type of calculation?



--
= = = = = = =
HTH :)
Dana DeLouis
.