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
http://www.officekb.com