Solved

# Problem Couting Dates in Named Range

Posted on 2011-10-17
188 Views
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?

Thanks for your help

Massimo
ProblemNamedRangeJPG.jpg
Problem.xls
0
Question by:mscola

LVL 50

Accepted Solution

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 Closing Comment

Thanks a lot barry

May I ask you one more question:

why: 10^10 ?

Massimo
0

LVL 50

Expert Comment

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 Comment

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

## Join & Write a Comment Already a member? Login.

### Suggested Solutions

Title # Comments Views Activity
VBA Excel Gantt 1 31
MS Excel Cell questions 5 40
Excel Formula: define specific name every 100 lines 6 29
Excel If tests 10 41
Sometimes we don't want to show zeros in our Excel spreadsheets. This is sometimes most evident in our charts. Look at the chart below, all the zero values are visible. I think that all will agree with the fact that zero values are not looking nice …
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

#### 746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

#### Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!