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.

05-Jan-18​	OPS​
12-Jan-18​	OPS​
19-Jan-18​	QA/OPS ​
26-Jan-18​	OPS​
02-Feb-18​	OPS ​
16-Feb-18​	QA​
23-Feb-18​	OPS​

<tbody> </tbody>

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