Counting events by month

**Mr2006** · 09-25-2012, 09:09 PM

Hi ALl,

I am trying to count the number of events happened per month in the folowing format:

January Feb March April May
Mark Sameul 5 8 2 0 4
Eduward Mathes 2 5 4 5 5

The data in the table where it will count is as the below:

01/01/2012
01/17/2012
01/5/2012
01/5/2012
01/7/2012

I tried to use countif(Range,Criteria) the range is where the date field, it is sheet3 A:A, and the criteria is "January" which is cell B2 but for somereason is not counting right......

help please.....

**FDibbins** · 09-25-2012, 11:49 PM

it probably has something to do wuth the format you have the months in.

can you attach a sample workbook please?

1. Click on Go Advanced
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

**Kevin UK** · 09-26-2012, 03:45 AM

Hi Mr2006

Something along these lines.

Formula:

Please Login or Register  to view this content.

**Mr2006** · 09-26-2012, 05:10 AM

I attached the file.

I used the sumproduct below but since I have lots of data, excel became very slow.

SUMPRODUCT(--(TEXT(Your Array,"mmmm")=Cell_Ref#),--(Your Array =EG:"Eduward"))

**Ace_XL** · 09-26-2012, 05:22 AM

I notice you are using COUNTIFS on the attached spreadsheet, so you are using a 2007 or later version of Excel. Hence use,

In B3
=COUNTIFS(Data!$B:$B,">="&B$2,Data!$B:$B,"<="&EOMONTH(B$2,0),Data!$A:$A,$A3)

P.S. update your profile to reflect your Excel verison!