asked on

CountIFS Date Range

Hi

Excel 2010

I have two tabs on my worksheet
Main and Stats

In the Main I have data as follows
Matter Date
1 01/09/2014
2 21/09/2014
3 03/10/2014

In the stats I have
Month Number
01/09/2014
01/102014

for the number column, I want to count all dates that correspond to the month and year in the Month column in the Stats tab

i.e. I would get the following based on the example data

Month Number
01/09/2014 2
01/102014 1

Any help would be appreciated

Glenn Ray

You can use an array formula to determine this.

If your Main data is in columns A&B and your Stats "Month" is in column A and you want the number of occurrences to appear in column B, you'd insert this formula (this is in cell B2, use [Ctrl]+[Shift]+[Enter] to enter):
=SUM(IF(MONTH(Main!$B$2:$B$51)&"-"&YEAR(Main!$B$2:$B$51)=MONTH(A2)&"-"&YEAR(A2),1,0))

Then copy that formula down as far as needed. I'm only testing through row 51; you'll want to change that value to the number of rows in your Main sheet.

Example file attached. It also has a PivotTable that verifies the results (using Group option on the dates to show month and year).

-Glenn
EE-Q28547994.xlsx

ASKER CERTIFIED SOLUTION

Rob Henson

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Glenn Ray

^Nice. Couldn't get much simpler.

Martin Liss

I've requested that this question be closed as follows:

Accepted answer: 500 points for Rob Henson's comment #a40415462

for the following reason:

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.