# Activity by Day in Month

#### Colmans

##### Board Regular
Hi

I want to run some analysis based on data where I can count records based on what day of the month the event occured. This is so I can see patterns of workload across an average month using several years data. How can I do this using the countif function (or alternative)?

Thanks in advance for feedback.

### Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Hi

Insert a pivot table. Date field in rows area and group by day. Date field in values area, aggregate by count.

Hi Colmans,

Like this?

Colmans.xlsx
ABCDEFGHI
1Date
205-Jul-18Day123456
303-Jul-19Count313020
405-Oct-19
501-Jun-21
601-Jul-21
701-Oct-22
802-Mar-21
903-Jun-19
1003-Jun-21
11
Sheet1
Cell Formulas
RangeFormula
D3:I3D3=SUM(IF(DAY(\$A\$2:\$A\$99999)=D\$2,1,0))
Press CTRL+SHIFT+ENTER to enter array formulas.

Hi Colmans,

Like this?

Colmans.xlsx
ABCDEFGHI
1Date
205-Jul-18Day123456
303-Jul-19Count313020
405-Oct-19
501-Jun-21
601-Jul-21
701-Oct-22
802-Mar-21
903-Jun-19
1003-Jun-21
11
Sheet1
Cell Formulas
RangeFormula
D3:I3D3=SUM(IF(DAY(\$A\$2:\$A\$99999)=D\$2,1,0))
Press CTRL+SHIFT+ENTER to enter array formulas.

Yes, but it doesn't work if I put the dates in dd/mm/yyyy format. I've also changed the data range to a named range but that shouldn't make a difference i'd have thought??

The format won't change the content. Excel holds dates as number of days since 1 Jan 1900 so if I format my dates column as General it looks like this and the formula still works:

If you format your dates as General and they still look like dates then they must be text data (in which my formula would return a #VALUE! error). There are a couple of ways around that but the best approach would be actually use a date, not text.

The format won't change the content. Excel holds dates as number of days since 1 Jan 1900 so if I format my dates column as General it looks like this and the formula still works:
View attachment 54260

If you format your dates as General and they still look like dates then they must be text data (in which my formula would return a #VALUE! error). There are a couple of ways around that but the best approach would be actually use a date, not text.
it actually gives me a zero value, no error message. I have the data in separate worksheet and that shouldn't matter either I think which is why i've used a named data range. I've formatted the dates same as the original suggestion

Don't forget that important note under the formula:
"Press CTRL+SHIFT+ENTER to enter array formulas"

Hi

Insert a pivot table. Date field in rows area and group by day. Date field in values area, aggregate by count.
How do you group by day? It seems to default to month and I cant work out how to change that?

Don't forget that important note under the formula:
"Press CTRL+SHIFT+ENTER to enter array formulas"
OK - Sorted thx for help

Replies
4
Views
143
Replies
2
Views
175
Replies
1
Views
216
Replies
3
Views
126
Replies
0
Views
78

1,203,483
Messages
6,055,665
Members
444,806
Latest member
tofanexcel

### 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?

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