Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMIF with defined name as calculated value
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("D2:D12,D15:D20") .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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMIF with defined name as calculated value
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMIF with defined name as calculated value
Sorry ...
I meant criteria ""RC[-3]"" ... Carim |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMIF with defined name as calculated value
If you're going to use references like RC[-2], then you'll want to use
..formulaR1C1 (not .formula). Asert wrote: 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("D2:D12,D15:D20") .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 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to use SUMIF to return sums between two values located in cells | Excel Worksheet Functions | |||
SUMIF function help | Excel Worksheet Functions | |||
Sumif of Sumif perhaps? | Excel Discussion (Misc queries) | |||
PIVOT TABLE - hiding records with CALCULATED item values equal to | Excel Discussion (Misc queries) | |||
SUMIF | Excel Worksheet Functions |