Problem Couting Dates in Named Range

Posted on 2011-10-17
Last Modified: 2012-08-14

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.


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:


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

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


    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


    see attached with those revisons

    regards, barry

    Author Closing Comment

    Thanks a lot barry

    May I ask you one more question:

    why: 10^10 ?

    LVL 50

    Expert Comment

    by:barry houdini
    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


    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

    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?


    Featured Post

    Courses: Start Training Online With Pros, Today

    Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

    Join & Write a Comment

    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.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now