I have been keeping track of my call log in excel. Since the nature of a
call log grows, i want to put dynmaic ranges in my formulas. I have been
using SUMPRODUCT to see how many calls have been by phone from all the other
offices.

Old
=SUMPRODUCT((MONTH(Data!$A$5:$A$683)=MONTH($A25))* (YEAR(Data!$A$5:$A$683)
=YEAR($A25))*(Data!$J$5:$J$683=J$18))

So now i have defined these ranges with
=OFFSET(Data!$J$5,0,0,COUNTA(Data!$J:$J),1)

New
=SUMPRODUCT((MONTH(Dates)=MONTH($A25))*(YEAR(Dates )=YEAR($A25))*(HelpDesk=J
$18))


This new formula works fine if i only use one dynamic range. I get a N/A
error when i add the HelpDesk range.

Another problem is when i am defining my dynamic range, i click on the
formula and the range is highlighted. When i scoll down to the bottom of my
range, there is an empty blank cell that is part of this range. Is that
giving me the error?

Thank you!

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200608/1