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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple dynamic ranges in a Sumproduct
range, there is an empty blank cell that is part of this range. Is that
giving me the error? If it causes the helpdesk range to be larger than the other ranges. When using arrays in sumproduct, they must be the same size or you will get #N/A. The only time I've seen they can be different sizes is when using a single cell, like Sumproduct(H1:H10*G1) "mmartens12 via OfficeKB.com" wrote: 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 |
Reply |
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 |