SUMIF with defined name as calculated value

[Guest]

I have a 2 worksheet workbook consisting of a summary page and a detail page.
The detail page contains data pulled from a Sequel table and the summary page
summarizes data from the detail page by category. The detail data consists
of identifiers, amounts and quantities.

The summary page columns are set to formulas programmatically using VBA.

Example of code for columns just pulling directly from a detail column (this
part is working):
With Range("C2:C12,C15:C20")
.Formula = "=SUMIF(Detail!Sort_Tkr,RC[-2],Detail!Amt_on_Deposit)"
End With

One of the columns is a summarization of a calculated value: Unit *
price/100, for which there is no column on the detail page. (There is a unit
column and a price column.)

I defined a new name (Mkt_on_Deposit) and set it to the following formula:
=PRODUCT(Detail!E2,Detail!U2,0.01)

The code to calculate the summary page amount by category is:
With Range("D212,D1520")
.Formula = "=SUMIF(Detail!Sort_Tkr,RC[-3],Detail!Mkt_on_Deposit)"
End With

This statement isn't working. I've tried several variations in syntax and
haven't been able to get it to work. I either get a #NUM or a #VALUE error,
depending upon whether I use SUMIF or SUMPRODUCT.

Anybody have suggestions? I can't provide actual examples of data because
it's proprietary.

Thanks

 

[Carim]

Hi Asert,

My guess would be it has to do with the criteria as it appears in your
formula: RC[-3] ...

Would test the macro with criteria such as : ""A"" to validate ...

HTH

Cheers
Carim

 

[Carim]

Sorry ...

I meant criteria ""RC[-3]"" ...

Carim

 

[Dave Peterson]

If you're going to use references like RC[-2], then you'll want to use
..formulaR1C1 (not .formula).

I have a 2 worksheet workbook consisting of a summary page and a detail page.
The detail page contains data pulled from a Sequel table and the summary page
summarizes data from the detail page by category. The detail data consists
of identifiers, amounts and quantities.

The summary page columns are set to formulas programmatically using VBA.

Example of code for columns just pulling directly from a detail column (this
part is working):
With Range("C2:C12,C15:C20")
.Formula = "=SUMIF(Detail!Sort_Tkr,RC[-2],Detail!Amt_on_Deposit)"
End With

One of the columns is a summarization of a calculated value: Unit *
price/100, for which there is no column on the detail page. (There is a unit
column and a price column.)

I defined a new name (Mkt_on_Deposit) and set it to the following formula:
=PRODUCT(Detail!E2,Detail!U2,0.01)

The code to calculate the summary page amount by category is:
With Range("D212,D1520")
.Formula = "=SUMIF(Detail!Sort_Tkr,RC[-3],Detail!Mkt_on_Deposit)"
End With

This statement isn't working. I've tried several variations in syntax and
haven't been able to get it to work. I either get a #NUM or a #VALUE error,
depending upon whether I use SUMIF or SUMPRODUCT.

Anybody have suggestions? I can't provide actual examples of data because
it's proprietary.

Thanks