Growing range within a Sumproduct.

[mmartens12 via OfficeKB.com]

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.

 

[Peo Sjoblom]

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]

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?

 

[mmartens12 via 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?

 

[mmartens12 via 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?

 

[mmartens12 via 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?