# counting occurrences in months

1. ## counting occurrences in months

Hi Guys,

I am trying to count how often a data set appears in each month.

I am trying to do it twice once with one argument and then again with two arguments ( i think)

I will try and explain against the sheet i have attached.

So...

first of all in cells E5 - P5 i would like to count how many times "Area one" appears in the list in the blue table in column N in each month in column H. So for example i can see "Area one" appears 3 times in the table in May 24 in column H, so in Cell F5 i would like it count 3.

Second off, I kind of what the same thing, so in cells E10 to P10 i want to do the same count, but this time only include ones that have "stage 3" in column L, so again if we take Area one as an example and the month of May, I can see Area one appears 3 times in may, but only twice with "Stage 3" in column L, So in cell F10 i would want it to count only 2

I hope that makes sense, sometimes i find it hard to articulate what im after :|

Luke

2. ## Re: counting occurrences in months

at the first look I think you need a Pivot Table.

3. ## Re: counting occurrences in months

Thanks for the response, that is really helpful, however, i uploaded a dummy sheet, the real sheet layout is really complicated and i struggle to get the pivot tables to work in it, i wondered if there were any formulas that could do the counting for me?

4. ## Re: counting occurrences in months

All stages:
Formula:
`Please Login or Register  to view this content.`
Only Stage 3:
Formula:
`Please Login or Register  to view this content.`

5. ## Re: counting occurrences in months

Unfortunately i dont have that functionality available, i thought i might be able to get away with some kind of COUNTIFS or SUMPRODUCT

6. ## Re: counting occurrences in months

Try, =COUNTIFS(\$N\$16:\$N\$28,\$D5,\$H\$16:\$H\$28,">="&E\$4,\$H\$16:\$H\$28,"<="&EOMONTH(E\$4,0)) in E5, copy across and down.

With Stage 3 in D9, =COUNTIFS(\$N\$16:\$N\$28,\$D10,\$H\$16:\$H\$28,">="&E\$9,\$H\$16:\$H\$28,"<="&EOMONTH(E\$9,0),\$L\$16:\$L\$28,\$D\$9) in E10, copy across and down.

7. ## Re: counting occurrences in months

This was exactly what i needed! works perfectly thank you :D

8. ## Re: counting occurrences in months

Also, if you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1