| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dynamic name reference in SUMPRODUCT
Formula:
SUMPRODUCT((ACCOUNT)*(AMOUNT)*(Period="2006-08")) works fine... it sums the amounts where period equak 2006-08 but Forumla: SUMPRODUCT(($A$2)*(AMOUNT)*(Period="2006-08")) doesn't work... $A$2="ACCOUNT" ACCOUNT=(A2:A65535) Data table Account Amount Period 7830 23400 2006-08 3220 -99900 2006-12 3440 -7100 2006-08 3560 2700 2006-08 8540 7900 2006-12 Is there anyone that could help, plse Best Regards |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Dynamic name reference in SUMPRODUCT
Don't really see how this works
=SUMPRODUCT((ACCOUNT)*(AMOUNT)*(Period="2006-08")) this will multiply the account number(s) by the amount(s) If you are summing Amounts for a specific account number that would be something like =SUMPRODUCT((ACCOUNT=3220)*(AMOUNT)*(Period="2006-08")) What do you have in A2 - a specific account number? "MIKWIN" wrote: Formula: SUMPRODUCT((ACCOUNT)*(AMOUNT)*(Period="2006-08")) works fine... it sums the amounts where period equak 2006-08 but Forumla: SUMPRODUCT(($A$2)*(AMOUNT)*(Period="2006-08")) doesn't work... $A$2="ACCOUNT" ACCOUNT=(A2:A65535) Data table Account Amount Period 7830 23400 2006-08 3220 -99900 2006-12 3440 -7100 2006-08 3560 2700 2006-08 8540 7900 2006-12 Is there anyone that could help, plse Best Regards |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Dynamic name reference in SUMPRODUCT
Thanks for bothering...
The formula sums all the AMOUNTs oi rows where PERIOD equals "2006-08" and ACCOUNT is not null. I would like to put "ACCOUNT" in cell $A$2 and put the cell reference $A$2 in the formula instead. BR "daddylonglegs" skrev: Don't really see how this works =SUMPRODUCT((ACCOUNT)*(AMOUNT)*(Period="2006-08")) this will multiply the account number(s) by the amount(s) If you are summing Amounts for a specific account number that would be something like =SUMPRODUCT((ACCOUNT=3220)*(AMOUNT)*(Period="2006-08")) What do you have in A2 - a specific account number? "MIKWIN" wrote: Formula: SUMPRODUCT((ACCOUNT)*(AMOUNT)*(Period="2006-08")) works fine... it sums the amounts where period equak 2006-08 but Forumla: SUMPRODUCT(($A$2)*(AMOUNT)*(Period="2006-08")) doesn't work... $A$2="ACCOUNT" ACCOUNT=(A2:A65535) Data table Account Amount Period 7830 23400 2006-08 3220 -99900 2006-12 3440 -7100 2006-08 3560 2700 2006-08 8540 7900 2006-12 Is there anyone that could help, plse Best Regards |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Dynamic name reference in SUMPRODUCT
Try
Indirect($A$2) "MIKWIN" wrote: Thanks for bothering... The formula sums all the AMOUNTs oi rows where PERIOD equals "2006-08" and ACCOUNT is not null. I would like to put "ACCOUNT" in cell $A$2 and put the cell reference $A$2 in the formula instead. BR "daddylonglegs" skrev: Don't really see how this works =SUMPRODUCT((ACCOUNT)*(AMOUNT)*(Period="2006-08")) this will multiply the account number(s) by the amount(s) If you are summing Amounts for a specific account number that would be something like =SUMPRODUCT((ACCOUNT=3220)*(AMOUNT)*(Period="2006-08")) What do you have in A2 - a specific account number? "MIKWIN" wrote: Formula: SUMPRODUCT((ACCOUNT)*(AMOUNT)*(Period="2006-08")) works fine... it sums the amounts where period equak 2006-08 but Forumla: SUMPRODUCT(($A$2)*(AMOUNT)*(Period="2006-08")) doesn't work... $A$2="ACCOUNT" ACCOUNT=(A2:A65535) Data table Account Amount Period 7830 23400 2006-08 3220 -99900 2006-12 3440 -7100 2006-08 3560 2700 2006-08 8540 7900 2006-12 Is there anyone that could help, plse Best Regards |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Dynamic name reference in SUMPRODUCT
Hi
INDIRECT converts string range description to range. But maybe you start from start - what do you want to do at all? I have a feeling there will be some better solution. Btw, having account range described in A2, you have always to check the number of rows in ranges Amount and Period, and compare it with number of rows you'll get from range described in A2 - SUMPRODUCT will work only, when it is same (all ranges involved must be of same dimension). And your original formula must be =SUMPRODUCT((ACCOUNT<0)*(Period="2006-08")*(AMOUNT)) , or =SUMPRODUCT((ACCOUNT<"")*(Period="2006-08")*(AMOUNT)) And I myself prefer other syntax, like =SUMPRODUCT(--(ACCOUNT<""),--(Period="2006-08"),AMOUNT) -- Arvi Laanemets ( My real mail address: arvi.laanemets<attarkon.ee ) "MIKWIN" wrote in message ... Formula: SUMPRODUCT((ACCOUNT)*(AMOUNT)*(Period="2006-08")) works fine... it sums the amounts where period equak 2006-08 but Forumla: SUMPRODUCT(($A$2)*(AMOUNT)*(Period="2006-08")) doesn't work... $A$2="ACCOUNT" ACCOUNT=(A2:A65535) Data table Account Amount Period 7830 23400 2006-08 3220 -99900 2006-12 3440 -7100 2006-08 3560 2700 2006-08 8540 7900 2006-12 Is there anyone that could help, plse Best Regards |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Dynamic name reference in SUMPRODUCT
I would like to select intervals and name them in order to use the same
intervals in many sheets: In A2 the interval could be (Account=3000) * (Account=<3999) and name SALES In A3 the interval could be (Account=4000) * (Account=<4999) and name OPERATIONAL_COST In A4 the interval could be (Account=5000) * (Account=<6999) and name OTHER_EXTERNAL_COST In A5 the interval could be (Account=7000) * (Account=<7699) and name LABOR_RELATED_COST BR "Arvi Laanemets" skrev: Hi INDIRECT converts string range description to range. But maybe you start from start - what do you want to do at all? I have a feeling there will be some better solution. Btw, having account range described in A2, you have always to check the number of rows in ranges Amount and Period, and compare it with number of rows you'll get from range described in A2 - SUMPRODUCT will work only, when it is same (all ranges involved must be of same dimension). And your original formula must be =SUMPRODUCT((ACCOUNT<0)*(Period="2006-08")*(AMOUNT)) , or =SUMPRODUCT((ACCOUNT<"")*(Period="2006-08")*(AMOUNT)) And I myself prefer other syntax, like =SUMPRODUCT(--(ACCOUNT<""),--(Period="2006-08"),AMOUNT) -- Arvi Laanemets ( My real mail address: arvi.laanemets<attarkon.ee ) "MIKWIN" wrote in message ... Formula: SUMPRODUCT((ACCOUNT)*(AMOUNT)*(Period="2006-08")) works fine... it sums the amounts where period equak 2006-08 but Forumla: SUMPRODUCT(($A$2)*(AMOUNT)*(Period="2006-08")) doesn't work... $A$2="ACCOUNT" ACCOUNT=(A2:A65535) Data table Account Amount Period 7830 23400 2006-08 3220 -99900 2006-12 3440 -7100 2006-08 3560 2700 2006-08 8540 7900 2006-12 Is there anyone that could help, plse Best Regards |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Vlookup - dynamic range reference? | Excel Discussion (Misc queries) | |||
| I want to be able to create a dynamic reference in excel, for exam | Excel Worksheet Functions | |||
| Reference / Copy Dynamic Data | Excel Discussion (Misc queries) | |||
| How to dynamically reference a dynamic named range | Excel Worksheet Functions | |||
| Dynamic reference to a sheet | Excel Worksheet Functions | |||
Linear Mode
