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