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)

Open in new window


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))

Open in new window


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

Thanks for your help

Massimo
ProblemNamedRangeJPG.jpg
Problem.xls
Massimo ScolaAsked:
Who is Participating?
 
barry houdiniConnect With a Mentor 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
 
Massimo ScolaAuthor Commented:
Thanks a lot barry

May I ask you one more question:

why: 10^10 ?

Massimo
0
 
barry houdiniCommented:
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
 
Massimo ScolaAuthor 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.

All Courses

From novice to tech pro — start learning today.