CountIf text strings per month per year

[ShelleyBelly]

Hi,

I'm totally new to posting but have found MrExcel a life saver on more than one occasion.

I would like to count the number of times a name appears given month and year. For example, Mark appears twice in july in 2010.

My spreadsheet is as follows:

A B
21-Jul-10 Mark
29-Jul-10 Pippa, Gerri, Mark, Lee, Ade
30-Jul-10 Pippa, Gerri, Nicola, Lee, Ade
3-Aug-10 Mark
4-Aug-10 Mark
5-Aug-10 Pippa, Ade, Gerri, Nicola, Jules
6-Aug-10 Pippa, Ade, Gerri, Nicola, Jules


I have already got the =COUNTIFS(B:B,"*" & D1 & "*") where D1 is the name. This works but obviously doesn't discriminate between months and years.

I've tried

=SUMPRODUCT(--(B:B="*" & Mark & "*"),--(MONTH(A:A)=8),--(YEAR(A:A)=2010))

But get #Name? . I'm not so sure the SumProduct is the way forward either.

Anybody any ideas, I have spent plenty of time looking through forums but haven't come across the right answer.

Many Thanks

 

[Haseeb Avarakkan]

Try,

=COUNTIFS(A:A,">="&DATE(2010,7,1),A:A,"<="&DATE(2010,7,31),B:B,"*"&D1&"*")

Where D1 is the name.

 

[ShelleyBelly]

Re: CountIf text strings between dates (SOLVED)

Hi Haseeb,

Just the thing I was looking for.

Many Thanks

Tom