Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple dynamic ranges in a Sumproduct
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SumProduct for multiple WORKBOOKS | Excel Worksheet Functions | |||
Dynamic Range with unused formula messing up x axis on dynamic graph | Charts and Charting in Excel | |||
Dynamic range names, multiple criteria, sumproduct | Excel Discussion (Misc queries) | |||
How to Link named ranges from multiple Workbooks into a single Wo. | Excel Discussion (Misc queries) | |||
compare unique identifiers in multiple ranges | Charts and Charting in Excel |