Using COUNTIF to number of in certain date ranges

**runtheyards** · 05-07-2012, 07:49 AM

I need some help to create a formula that will count the number of hits between certain date ranges.

The dates are stored in the format below and are in cells E5:E87

(abstract of data)
01/04/50
12/11/72
22/11/66
15/11/72
02/03/53
01/12/70
30/09/58

What i want to do i i make a graph of with the following rules

People in their...
20 = 1992 - 2002 (return number of hits between the date ranges)
30 = 1982 - 1992
40 = 1972 - 1982
50 = 1962 - 1972
60 = 1952 - 1962
70 = 1942 - 1952
80 = 1932 - 1942
90 = 1922 - 1932

Could some one help me with the formula and i think i have to use wildcards to just take the year and ignore the day and month.

Any help would be much appreciated.

Thanks.

**Fotis1991** · 05-07-2012, 07:59 AM

Hi and welcome to the forum.

Your request is not clear(At least, to me) Could you explai a little more or better to upload a small sample workbook?

To get the year of a date, use this.

=YEAR(A1)

**runtheyards** · 05-07-2012, 08:13 AM

Screen Shot 2012-05-07 at 1.01.44 PM.jpg
Ok what i need to so is count how many people are in their 30's, 40's, 50s...etc. I figured i could have an formula for each age groups.

so basically in simple terms for people that are in their 30's look at column E5:E87 and count the amount of people who's birth year is between 82 and 92 and then return amount of matches.

and put answer in cell L106 Screen Shot 2012-05-07 at 1.04.57 PM.png

sorry for the confusion i'am not the best at explaining excel as i very new to this.

**Fotis1991** · 05-07-2012, 08:21 AM

Try this

=SUMPRODUCT((YEAR(E5:E87)<=1992))*(YEAR(E5:E87)>=1982))))

OR

Please, upload a sample WORKBOOK. Not a picture.

**runtheyards** · 05-07-2012, 08:25 AM

SeeScotland - Client List Oct 2012.xls

heres the file

**Fotis1991** · 05-07-2012, 10:02 AM

Using a helper column, i think you have your result.

Please Login or Register  to view this content.

Using COUNTIF to number of in certain date ranges

LinkBack

Thread Tools

Rate This Thread

Display

Using COUNTIF to number of in certain date ranges

Re: Using COUNTIF to number of in certain date ranges

Re: Using COUNTIF to number of in certain date ranges

Re: Using COUNTIF to number of in certain date ranges

Re: Using COUNTIF to number of in certain date ranges

Re: Using COUNTIF to number of in certain date ranges

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions