COUNTIF for multiple critera, and for specifc months

[gorillawar]

Hello,

I am trying to count the number of word occurrences for a specific word within arange, but only if it matches a specific month.



[TABLE="width: 121"]
<tbody>[TR]
[TD="width: 79"]

05-Jan-18​

[/TD]
[TD="width: 83"]

OPS​

[/TD]
[/TR]
[TR]
[TD="width: 79"]

12-Jan-18​

[/TD]
[TD="width: 83"]

OPS​

[/TD]
[/TR]
[TR]
[TD="width: 79"]

19-Jan-18​

[/TD]
[TD="width: 83"]

QA/OPS ​

[/TD]
[/TR]
[TR]
[TD="width: 79"]

26-Jan-18​

[/TD]
[TD="width: 83"]

OPS​

[/TD]
[/TR]
[TR]
[TD="width: 79"]

02-Feb-18​

[/TD]
[TD="width: 83"]

OPS ​

[/TD]
[/TR]
[TR]
[TD="width: 79"]

16-Feb-18​

[/TD]
[TD="width: 83"]

QA​

[/TD]
[/TR]
[TR]
[TD="width: 79"]

23-Feb-18​

[/TD]
[TD="width: 83"]

OPS​

[/TD]
[/TR]
</tbody>[/TABLE]

I have tried =COUNTIF(B1:B7,"QA/OPS")+COUNTIF(B1:B7,"OPS")*(MONTH(A1:A7)=1)


It just returns a 1, instead of a 4, since there are 4occurrences of the word “OPS” within January

Any help is appreciated

Thanks,

 

[Fluff]

How about
=SUMPRODUCT(ISNUMBER(SEARCH("OPS",B1:B7))*(MONTH(A1:A7)=1))

 

[Scott Huish]

Try this:

=SUMPRODUCT(--((B1:B7="OPS")+(B1:B7="QA/OPS")),--(MONTH(A1:A7)=1))

Note that if those are the only 2 options with OPS in them then Fluff's formula is better, but if you need to specifically isolate only those 2 item, then mine does that.

 

[gorillawar]

This is perfect, thank you.

 

[Fluff]

Not sure which of us you are referring to, but glad we could help & thanks for the feedback