software

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$113gt;=DATEVALUE( quot;06/04/2004quot;),IF(Payments!$A$2:Payments!$A$113lt;=DATEVALUE (quot;05/04/2005quot;),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_agt;=DATEVALUE(quot;06/04/2004quot;),IF(payments_alt;=DATEVALUE(quot;05/04/2005quot;),IF(payments_h=$A6,payments_e,0),0),0))}
--------------------Any suggestions?

Thanks --
willee
------------------------------------------------------------------------
willee's Profile: www.excelforum.com/member.php...oamp;userid=31189
View this thread: www.excelforum.com/showthread...hreadid=508589If 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$13gt;=DATE(2004,6,4)),--(Payments!$A$2:$A$13lt;=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

www.contextures.com/xlNames01.html#Dynamic

--
Regards,

Peo Sjoblom

Portland, Oregon

quot;willeequot; gt; wrote in
message ...
gt;
gt; Hello folks,
gt;
gt; Been scratching my head for a couple of hours trying to avoid having to
gt; resort to VBA. Hopefully, you can help me with the following...
gt;
gt; I've used the Conditional Sum Wizard to set the value of a cell
gt; depending on multiple conditions and that seems to work fine, but I'd
gt; like to be able to use some dynamic, named ranges instead of absolute
gt; cell references in my formula.
gt;
gt; Here's the formula that works with the absolute references,
gt;
gt; Code:
gt; --------------------
gt;
gt; {=SUM(IF(Payments!$A$2:Payments!$A$113gt;=DATEVALUE( quot;06/04/2004quot;),IF(Payments!$A$2:Payments!$A$113lt;=DATEVALUE (quot;05/04/2005quot;),IF(Payments!$H$2:Payments!$H$113=$A6,Paymen ts!$E$2:Payments!$E$113,0),0),0))}
gt; --------------------
gt;
gt;
gt; What I'd really like to do is to replace the hard-coded reference to
gt; row 113 as addtional rows are appended. I thought my best approach
gt; would be to replace the cell range with a named range, as per the
gt; following, but it didn't work.
gt;
gt; Code:
gt; --------------------
gt;
gt; {=SUM(IF(payments_agt;=DATEVALUE(quot;06/04/2004quot;),IF(payments_alt;=DATEVALUE(quot;05/04/2005quot;),IF(payments_h=$A6,payments_e,0),0),0))}
gt; --------------------
gt;
gt;
gt; Any suggestions?
gt;
gt; Thanks
gt;
gt;
gt; --
gt; willee
gt; ------------------------------------------------------------------------
gt; willee's Profile:
gt; www.excelforum.com/member.php...oamp;userid=31189
gt; View this thread: www.excelforum.com/showthread...hreadid=508589
gt;
Peo Sjoblom Wrote:
gt; If you want to sum the Payment column E where A is between certain dates
gt; and
gt; where column H = A6 then you can use
gt;
gt; =SUMPRODUCT(--(Payments!$A$2:$A$13gt;=DATE(2004,6,4)),--(Payments!$A$2:$A$13lt;=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_agt;=DATE(2004,6,4)),--(payments_range_alt;=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: www.excelforum.com/member.php...oamp;userid=31189
View this thread: www.excelforum.com/showthread...hreadid=508589That 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

quot;willeequot; gt; wrote in
message ...
gt;
gt; Peo Sjoblom Wrote:
gt;gt; If you want to sum the Payment column E where A is between certain dates
gt;gt; and
gt;gt; where column H = A6 then you can use
gt;gt;
gt;gt; =SUMPRODUCT(--(Payments!$A$2:$A$13gt;=DATE(2004,6,4)),--(Payments!$A$2:$A$13lt;=DATE(2005,5,4)),--(Payments!$H$2:$H$13=$A6),Payments!$E$2:$E$13)
gt;
gt; Thank you! That works, but it still uses absolute references. I've no
gt; problems defining dynamic ranges, but I can't figure out how to
gt; implement dynamic ranges in the SUMPRODUCT formula you've kindly
gt; provided.
gt;
gt; Is it possible to use something like this
gt;
gt; Code:
gt; --------------------
gt;
gt; =SUMPRODUCT(--(payments_range_agt;=DATE(2004,6,4)),--(payments_range_alt;=DATE(2005,5,4)),--(payments_range_h=$A6),payments_range_e)
gt; --------------------
gt;
gt; where, for example,
gt;
gt; Code:
gt; --------------------
gt; payments_range_e = OFFSET(Payments!$E$1,0,0,COUNTA(Payments!$E:$E),1)
gt; --------------------
gt;
gt;
gt; ?
gt;
gt;
gt; --
gt; willee
gt; ------------------------------------------------------------------------
gt; willee's Profile:
gt; www.excelforum.com/member.php...oamp;userid=31189
gt; View this thread: www.excelforum.com/showthread...hreadid=508589
gt;
Peo Sjoblom Wrote:
gt; That is the correct sumproduct formula but you need to change the
gt; dynamic
gt; range
gt;
gt; payments_range_e
gt;
gt; would be
gt;
gt; =OFFSET(Payments!$E$2,0,0,COUNTA(Payments!$E$2:$E$ 65536),1)
gt;
gt; since it starts in E2.
gt;
gt; You could still use the counta($E:$E) but then you need offset by one
gt; if you
gt; have a header in E1
gt; or else you will include that as well
gt;
gt; =OFFSET(Payments!$E$2,0,0,COUNTA(Payments!$E:$E)-1,1)
gt;
gt; assuming you have a header. Do the same for the other ranges
gt;
gt; Personally I would probably use a large enough range in a defined name
gt; without using a dynamic ranges since
gt; if you by any chance would have a different count of items/values in
gt; one of
gt; the columns it would return a
gt; #VALUE! error (array formulas like these need same sized ranges) and
gt; it
gt; might be hard to spot but if you know these ranges never will go
gt; beyond
gt; let's say 10000 cells you could define a name for
gt; Payments!$A$2:$A$10000 and
gt; so on, that way the ranges would always be equally sized and you
gt; wouldn't
gt; 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: www.excelforum.com/member.php...oamp;userid=31189
View this thread: www.excelforum.com/showthread...hreadid=508589