Countif cell fall within date range

Mr Peabody

New Member
Joined
Oct 15, 2002
Messages
2
I have a spreadsheet with a number of dates
in a column

I would like to create a table which counts how many cells fall within each month

I ve tried countif but can only use one criteria - i need something like countif cells are >= 01oct02 and <=31oct02

can u help
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

B_2

Board Regular
Joined
Aug 24, 2002
Messages
210
=SUMPRODUCT((B3:B100>=DATE(2002,1,1))*(B3:B100<=DATE(2002,1,31)))


just substitue your dates for mine in the order shown
This message was edited by B_2 on 2002-10-16 11:55
 

Mark W.

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,654
Formula revised below...
This message was edited by Mark W. on 2002-10-16 13:13
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
A bit nicer maybe...
Book3
ABCD
1Dates2002
28/7/2002Jan0
38/12/2002Feb0
48/17/2002Mar0
58/22/2002Apr0
68/27/2002May0
79/1/2002Jun0
89/6/2002Jul0
99/11/2002Aug5
109/16/2002Sep6
119/21/2002Oct3
129/26/2002Nov0
1310/1/2002Dec0
1410/6/2002
1510/11/2002
16
Sheet1


The formula in D2 is...

=SUMPRODUCT((MONTH($A$2:$A$15)=MONTH(C2&0))*(YEAR($A$2:$A$15)=$D$1))
 

inarbeth

Well-known Member
Joined
Apr 8, 2002
Messages
912

ADVERTISEMENT

Mark can you double check your formula - it always gives the answer 4.
 

IML

MrExcel MVP
Joined
Feb 15, 2002
Messages
1,743
If you want to do this for every month, you could also consider a pivot table.
Put your date as you column heading and count of date as your data. Then right click on the date in the pivot table, select group and show detail, group, and select Months.
 

Mark W.

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,654

ADVERTISEMENT

Oops!! You're right! Thanks, inarbeth. The formula should be...
Book1
ABCDEFGHIJK
1Date3
210/1/2002
310/24/2002
411/30/2002
510/15/2002
6
7
8
9
Sheet6


Note: This is an array formula which must be entered using the Control+Shift+Enter key combination. The outermost braces, { }, are not entered by you -- they're supplied by Excel in recognition of a properly entered array formula. For more on array formulas see the Excel Help topic for "About array formulas and how to enter them".
This message was edited by Mark W. on 2002-10-16 13:14
 

IML

MrExcel MVP
Joined
Feb 15, 2002
Messages
1,743
You could one of these two arrays
=COUNT(IF(MONTH(A2:A5)=10,A2:A5,""))
=SUM(IF(MONTH(A2:A5)=10,1,0))

or even
=COUNTIF(A2:A5,">"&DATE(YEAR(NOW()),10,0))-COUNTIF(A2:A5,">"&DATE(YEAR(NOW()),10+1,0))
This message was edited by IML on 2002-10-16 12:47
 

Forum statistics

Threads
1,143,746
Messages
5,720,619
Members
422,293
Latest member
camillel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top