Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Growing range within a Sumproduct.
I am keepting track of all the calls i get so the range changes daily. In A
is the date and column J is who took the call. Is there any way to take this formula and have it refer to one place for the range end as my table grows? =SUMPRODUCT((MONTH(Data!$A$5:$A$670)=MONTH($A25))* (YEAR(Data!$A$5:$A$670) =YEAR($A25))*(Data!$J$5:$J$670=C$18)) Thanks. -- Message posted via http://www.officekb.com |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Growing range within a Sumproduct.
You can use dynamic ranges, description here
http://www.contextures.com/xlNames01.html#Dynamic -- Regards, Peo Sjoblom Excel 95 - Excel 2007 Northwest Excel Solutions www.nwexcelsolutions.com "mmartens12 via OfficeKB.com" <u24614@uwe wrote in message news:64193960a3b28@uwe... I am keepting track of all the calls i get so the range changes daily. In A is the date and column J is who took the call. Is there any way to take this formula and have it refer to one place for the range end as my table grows? =SUMPRODUCT((MONTH(Data!$A$5:$A$670)=MONTH($A25))* (YEAR(Data!$A$5:$A$670) =YEAR($A25))*(Data!$J$5:$J$670=C$18)) Thanks. -- Message posted via http://www.officekb.com |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Growing range within a Sumproduct.
That is pretty slick! Thanks.
I followed the directions on that website you gave me and created some dynamic ranges. My formulas work great with one dynamic range but gets a N/A error when i put another range into the formula. New =SUMPRODUCT((MONTH(Dates)=MONTH($A19))*(YEAR(Dates )=YEAR($A19))*(Systems=O$18) ) Old =SUMPRODUCT((MONTH(Data!$A$5:$A$645)=MONTH($A20))* (YEAR(Data!$A$5:$A$645) =YEAR($A20))*(Data!$E$5:$E$645=O$18)) Here is my Dynamic range =OFFSET(Data!$E$4,1,0,COUNTA(Data!$E:$E),1) The results: New = N/A old = right answer What can i do? -- Message posted via http://www.officekb.com |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Growing range within a Sumproduct.
That is pretty slick! Thanks.
I followed the directions on that website you gave me and created some dynamic ranges. My formulas work great with one dynamic range but gets a N/A error when i put another range into the formula. New =SUMPRODUCT((MONTH(Dates)=MONTH($A19))*(YEAR(Dates )=YEAR($A19))*(Systems=O$18) ) Old =SUMPRODUCT((MONTH(Data!$A$5:$A$645)=MONTH($A20))* (YEAR(Data!$A$5:$A$645) =YEAR($A20))*(Data!$E$5:$E$645=O$18)) Here is my Dynamic range =OFFSET(Data!$E$4,1,0,COUNTA(Data!$E:$E),1) The results: New = N/A old = right answer What can i do? -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200608/1 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Growing range within a Sumproduct.
That is pretty slick! Thanks.
I followed the directions on that website you gave me and created some dynamic ranges. My formulas work great with one dynamic range but gets a N/A error when i put another range into the formula. New =SUMPRODUCT((MONTH(Dates)=MONTH($A19))*(YEAR(Dates )=YEAR($A19))*(Systems=O$18) ) Old =SUMPRODUCT((MONTH(Data!$A$5:$A$645)=MONTH($A20))* (YEAR(Data!$A$5:$A$645) =YEAR($A20))*(Data!$E$5:$E$645=O$18)) Here is my Dynamic range =OFFSET(Data!$E$4,1,0,COUNTA(Data!$E:$E),1) The results: New = N/A old = right answer What can i do? -- Message posted via http://www.officekb.com |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Growing range within a Sumproduct.
That is pretty slick! Thanks.
I followed the directions on that website you gave me and created some dynamic ranges. My formulas work great with one dynamic range but gets a N/A error when i put another range into the formula. New =SUMPRODUCT((MONTH(Dates)=MONTH($A19))*(YEAR(Dates )=YEAR($A19))*(Systems=O$18) ) Old =SUMPRODUCT((MONTH(Data!$A$5:$A$645)=MONTH($A20))* (YEAR(Data!$A$5:$A$645) =YEAR($A20))*(Data!$E$5:$E$645=O$18)) Here is my Dynamic range =OFFSET(Data!$E$4,1,0,COUNTA(Data!$E:$E),1) The results: New = N/A old = right answer What can i do? -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200608/1 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SumProduct with criteria list | Excel Worksheet Functions | |||
Dynamic Range with unused formula messing up x axis on dynamic graph | Charts and Charting in Excel | |||
Using a worksheet name from a range | Excel Worksheet Functions | |||
Cannot Expand Named Range - when size of the Range exceeds | Excel Discussion (Misc queries) | |||
sumproduct in a range | Excel Worksheet Functions |