Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Sum Wizard with dynamic named ranges
Hello folks, Been scratching my head for a couple of hours trying to avoid having to resort to VBA. Hopefully, you can help me with the following... I've used the Conditional Sum Wizard to set the value of a cell depending on multiple conditions and that seems to work fine, but I'd like to be able to use some dynamic, named ranges instead of absolute cell references in my formula. Here's the formula that works with the absolute references, Code: -------------------- {=SUM(IF(Payments!$A$2:Payments!$A$113=DATEVALUE( "06/04/2004"),IF(Payments!$A$2:Payments!$A$113<=DATEVALUE ("05/04/2005"),IF(Payments!$H$2:Payments!$H$113=$A6,Paymen ts!$E$2:Payments!$E$113,0),0),0))} -------------------- What I'd really like to do is to replace the hard-coded reference to row 113 as addtional rows are appended. I thought my best approach would be to replace the cell range with a named range, as per the following, but it didn't work. Code: -------------------- {=SUM(IF(payments_a=DATEVALUE("06/04/2004"),IF(payments_a<=DATEVALUE("05/04/2005"),IF(payments_h=$A6,payments_e,0),0),0))} -------------------- Any suggestions? Thanks :) -- willee ------------------------------------------------------------------------ willee's Profile: http://www.excelforum.com/member.php...o&userid=31189 View this thread: http://www.excelforum.com/showthread...hreadid=508589 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Sum Wizard with dynamic named ranges
If you want to sum the Payment column E where A is between certain dates and
where column H = A6 then you can use =SUMPRODUCT(--(Payments!$A$2:$A$13=DATE(2004,6,4)),--(Payments!$A$2:$A$13<=DATE(2005,5,4)),--(Payments!$H$2:$H$13=$A6),Payments!$E$2:$E$13) no need to array enter if want to use a dynamic range which can be found here http://www.contextures.com/xlNames01.html#Dynamic -- Regards, Peo Sjoblom Portland, Oregon "willee" wrote in message ... Hello folks, Been scratching my head for a couple of hours trying to avoid having to resort to VBA. Hopefully, you can help me with the following... I've used the Conditional Sum Wizard to set the value of a cell depending on multiple conditions and that seems to work fine, but I'd like to be able to use some dynamic, named ranges instead of absolute cell references in my formula. Here's the formula that works with the absolute references, Code: -------------------- {=SUM(IF(Payments!$A$2:Payments!$A$113=DATEVALUE( "06/04/2004"),IF(Payments!$A$2:Payments!$A$113<=DATEVALUE ("05/04/2005"),IF(Payments!$H$2:Payments!$H$113=$A6,Paymen ts!$E$2:Payments!$E$113,0),0),0))} -------------------- What I'd really like to do is to replace the hard-coded reference to row 113 as addtional rows are appended. I thought my best approach would be to replace the cell range with a named range, as per the following, but it didn't work. Code: -------------------- {=SUM(IF(payments_a=DATEVALUE("06/04/2004"),IF(payments_a<=DATEVALUE("05/04/2005"),IF(payments_h=$A6,payments_e,0),0),0))} -------------------- Any suggestions? Thanks :) -- willee ------------------------------------------------------------------------ willee's Profile: http://www.excelforum.com/member.php...o&userid=31189 View this thread: http://www.excelforum.com/showthread...hreadid=508589 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Sum Wizard with dynamic named ranges
Peo Sjoblom Wrote: If you want to sum the Payment column E where A is between certain dates and where column H = A6 then you can use =SUMPRODUCT(--(Payments!$A$2:$A$13=DATE(2004,6,4)),--(Payments!$A$2:$A$13<=DATE(2005,5,4)),--(Payments!$H$2:$H$13=$A6),Payments!$E$2:$E$13) Thank you! That works, but it still uses absolute references. I've no problems defining dynamic ranges, but I can't figure out how to implement dynamic ranges in the SUMPRODUCT formula you've kindly provided. Is it possible to use something like this Code: -------------------- =SUMPRODUCT(--(payments_range_a=DATE(2004,6,4)),--(payments_range_a<=DATE(2005,5,4)),--(payments_range_h=$A6),payments_range_e) -------------------- where, for example, Code: -------------------- payments_range_e = OFFSET(Payments!$E$1,0,0,COUNTA(Payments!$E:$E),1) -------------------- ? -- willee ------------------------------------------------------------------------ willee's Profile: http://www.excelforum.com/member.php...o&userid=31189 View this thread: http://www.excelforum.com/showthread...hreadid=508589 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Sum Wizard with dynamic named ranges
That is the correct sumproduct formula but you need to change the dynamic
range payments_range_e would be =OFFSET(Payments!$E$2,0,0,COUNTA(Payments!$E$2:$E$ 65536),1) since it starts in E2. You could still use the counta($E:$E) but then you need offset by one if you have a header in E1 or else you will include that as well =OFFSET(Payments!$E$2,0,0,COUNTA(Payments!$E:$E)-1,1) assuming you have a header. Do the same for the other ranges Personally I would probably use a large enough range in a defined name without using a dynamic ranges since if you by any chance would have a different count of items/values in one of the columns it would return a #VALUE! error (array formulas like these need same sized ranges) and it might be hard to spot but if you know these ranges never will go beyond let's say 10000 cells you could define a name for Payments!$A$2:$A$10000 and so on, that way the ranges would always be equally sized and you wouldn't get an error -- Regards, Peo Sjoblom Portland, Oregon "willee" wrote in message ... Peo Sjoblom Wrote: If you want to sum the Payment column E where A is between certain dates and where column H = A6 then you can use =SUMPRODUCT(--(Payments!$A$2:$A$13=DATE(2004,6,4)),--(Payments!$A$2:$A$13<=DATE(2005,5,4)),--(Payments!$H$2:$H$13=$A6),Payments!$E$2:$E$13) Thank you! That works, but it still uses absolute references. I've no problems defining dynamic ranges, but I can't figure out how to implement dynamic ranges in the SUMPRODUCT formula you've kindly provided. Is it possible to use something like this Code: -------------------- =SUMPRODUCT(--(payments_range_a=DATE(2004,6,4)),--(payments_range_a<=DATE(2005,5,4)),--(payments_range_h=$A6),payments_range_e) -------------------- where, for example, Code: -------------------- payments_range_e = OFFSET(Payments!$E$1,0,0,COUNTA(Payments!$E:$E),1) -------------------- ? -- willee ------------------------------------------------------------------------ willee's Profile: http://www.excelforum.com/member.php...o&userid=31189 View this thread: http://www.excelforum.com/showthread...hreadid=508589 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Sum Wizard with dynamic named ranges
Peo Sjoblom Wrote: That is the correct sumproduct formula but you need to change the dynamic range payments_range_e would be =OFFSET(Payments!$E$2,0,0,COUNTA(Payments!$E$2:$E$ 65536),1) since it starts in E2. You could still use the counta($E:$E) but then you need offset by one if you have a header in E1 or else you will include that as well =OFFSET(Payments!$E$2,0,0,COUNTA(Payments!$E:$E)-1,1) assuming you have a header. Do the same for the other ranges Personally I would probably use a large enough range in a defined name without using a dynamic ranges since if you by any chance would have a different count of items/values in one of the columns it would return a #VALUE! error (array formulas like these need same sized ranges) and it might be hard to spot but if you know these ranges never will go beyond let's say 10000 cells you could define a name for Payments!$A$2:$A$10000 and so on, that way the ranges would always be equally sized and you wouldn't get an error In theory, there shouldn't be a differing number of values across each column, however, point taken. I've changed the ranges to cover up to row 65536 instead of being dynamic. Thanks again! :) -- willee ------------------------------------------------------------------------ willee's Profile: http://www.excelforum.com/member.php...o&userid=31189 View this thread: http://www.excelforum.com/showthread...hreadid=508589 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sum of named ranges conditional to date? | Excel Worksheet Functions | |||
Dynamic Named Ranges | Excel Discussion (Misc queries) | |||
Problem with Dynamic Named Ranges | Excel Worksheet Functions | |||
Excel2K: Is it possible to use dynamic named ranges in custom data validation formula? | Excel Discussion (Misc queries) | |||
Named dynamic ranges, copied worksheets and graph source data | Charts and Charting in Excel |