# Problem Couting Dates in Named Range

Hi

I've got a dynamic named range called CustomerSince . I used following formula but, as you can see on the picture and on the attached spreadsheet, it doesn't work properly. The formula should add only those cells into the named range that have a value.

``````=OFFSET(Date!\$A\$2;;;COUNTA(Date!\$A:\$A)-COUNTA(Date!\$A\$1:\$A\$1);1)
``````

Furthermore, I am trying to determine the date when the customer first orderer something from the company. I used following formula:

``````=SUMPRODUCT((MONTH(CustomerSince)=1)*(YEAR(CustomerSince)=2011))
``````

Where 1 is for January etc. But this formula doesn't work either. Is it because the named range is screwed up?

Massimo
ProblemNamedRangeJPG.jpg
Problem.xls
###### Who is Participating?

Commented:
You can't use that method for a dynamic named range here because it assumes no blanks. Try this to define the range

=Date!\$A\$2:INDEX(Date!\$A:\$A,MATCH(10^10,Date!\$A:\$A))

which defines the range down to the last number (date)

and the formula still won't work because it doesn't like the blanks either, try changing to this formula in E2 copied down

=SUMPRODUCT((MONTH(0&CustomerSince)=ROWS(E\$2:E2))*(YEAR(0&CustomerSince)=2011))

see attached with those revisons

regards, barry
27400333.xls
0

Author Commented:
Thanks a lot barry

May I ask you one more question:

why: 10^10 ?

Massimo
0

Commented:
For the lookup formula to work you make the lookup value a value bigger than any value found in the range, this is more normal

=LOOKUP(9.99E+307,A:A)

but with dates 10^10 is big enough to be greater than any date value in Excel, in fact 10^7 would be sufficient

regards, barry
0

Author Commented:
Hi Barry
Thanks for the information

I tried the formula and it seems to work with numbers/dates only.
How would I have to change the formula if I want to include words in a dynamic range?

Thanks
Massimo
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.