• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 237
  • Last Modified:

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
0
Massimo Scola
Asked:
Massimo Scola
  • 2
  • 2
1 Solution
 
barry houdiniCommented:
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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now