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

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
>

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

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

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

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

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

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

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

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