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

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

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

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

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

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