How do I count number of occurences in the last 4 months?

[blue sky]

I have hundreds of names in, say, column A and the date I saw that person in, say, column B. The data goes back several years. What I am after is a way to count the number of times I have seen each person over, say, the last 4 month period. I might need to extend this to six months.

Can a formula be used to work this out as I have no knowledge of VBA.

Your help very much appreciated.

 

[VBA Geek]

Try this


=COUNTIF(B:B,">="&EDATE(TODAY(),-4))

 

[Aladin Akyurek]

I have hundreds of names in, say, column A and the date I saw that person in, say, column B. The data goes back several years. What I am after is a way to count the number of times I have seen each person over, say, the last 4 month period. I might need to extend this to six months.

Can a formula be used to work this out as I have no knowledge of VBA.

Your help very much appreciated.

Something like;

=COUNTIFS(A:A,E1,B:B,">="&EDATE(TODAY(),-F1),B:B,"<="&TODAY())

where E1 houses a relevant person and F1 is the last N month parameter value like 4.

 

[blue sky]

Thanks for the incredibly fast replies! However, what I am really after is the number of occurances of a name in column A in any previous 4 month period in column B. Hope this makes sense!

 

[VBA Geek]

Thanks for the incredibly fast replies! However, what I am really after is the number of occurances of a name in column A in any previous 4 month period in column B. Hope this makes sense!

can you post a sample of your data also containing the expected result so we can provide (hopefully) the exact formula

 

[richarddylan261]

I have hundreds of names in, say, column A and the date I saw that person in, say, column B. The data goes back several years. What I am after is a way to count the number of times I have seen each person over, say, the last 4 month period. I might need to extend this to six months.

Can a formula be used to work this out as I have no knowledge of VBA.

Your help very much appreciated.

Would you like to share some pictures of your excel work because i want take your concept may it will helpful for me. Thanks in advance

 

[Aladin Akyurek]

Thanks for the incredibly fast replies! However, what I am really after is the number of occurances of a name in column A in any previous 4 month period in column B. Hope this makes sense!

Doesn't post #3 work for you? If it doesn't, clarify what you get or try to provide a small sample.

 

[blue sky]

can you post a sample of your data also containing the expected result so we can provide (hopefully) the exact formula

[TABLE="width: 537"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]NAME[/TD]
[TD]DATE[/TD]
[TD]No Times in "N" months[/TD]
[TD]No Months is[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]Jo[/TD]
[TD="align: right"]12/02/2015[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Fred[/TD]
[TD="align: right"]12/08/2015[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Max[/TD]
[TD="align: right"]12/10/2015[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jo[/TD]
[TD="align: right"]12/06/2015[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jo[/TD]
[TD="align: right"]12/09/2015[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Fred[/TD]
[TD="align: right"]01/04/2015[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Andy[/TD]
[TD="align: right"]01/07/2015[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jeff[/TD]
[TD="align: right"]10/08/2015[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Max[/TD]
[TD="align: right"]14/09/2015[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Dave[/TD]
[TD="align: right"]16/08/2015[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jo[/TD]
[TD="align: right"]18/10/2015[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[VBA Geek]

Thanks. Are you looking to have a list of unique names in column, say C, and next to them the count?

 

[blue sky]

Thanks. Are you looking to have a list of unique names in column, say C, and next to them the count?

No, nothing in column C. It almost an excercise in counting duplicate name.

Update: Many thanks for pointing me in the right direction and its now working a treat! Thank you SO much