count # of times word appears in dataset based also on another columns additional value

Hi,

I am trying to find a solution that will provide me with the number of times a certain word is present in a data set, "dog" for example, but only if another specific criteria is met. I also would like to be able to input the the word and additional criteria into cells that will be referenced by the formula to return the result. Example: if column B has data and I type into my reference cell D2 "dog" and the date 2/1/2016 into reference cell E2, and dog appears 6 times in column B, but column C only has two February dates, i want to return the number of times that dog appeared in February of 2016 only (example 3 times). I have used the formula below to find the total number but can not figure out a way to add the additional criteria. Any help would be greatly appreciated. Thanks,

SUMPRODUCT((LEN(UPPER($B$2:$B$8))-LEN(SUBSTITUTE(UPPER($B$2:$B$8),UPPER($E2),"")))/LEN(UPPER($E2)))

Many members are unable to see images in the *.png format due to forum compatibility issues with some browsers.

If you need to post an image post it in the *.jpg format.

Even better than posting images... post a SMALL sample file. That way we can test solutions directly in the file with the relevant data.

This array formula** seems to do what I think you're asking for...

=SUM(IF(MONTH($A$2:$A$8)=MONTH($F2),(LEN(UPPER($B$2:$B$8))-LEN(SUBSTITUTE(UPPER($B$2:$B$8),UPPER($E2),"")))/LEN(UPPER($E2))))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Like this...

=SUM(IF(TEXT($A$2:$A$8,"mmmyyyy")=TEXT($F2,"mmmyyyy"),(LEN(UPPER($B$2:$B$8))-LEN(SUBSTITUTE(UPPER($B$2:$B$8),UPPER($E2),"")))/LEN(UPPER($E2))))

Still array entered.

You're welcome. Thanks for the feedback!

Like this...

=SUM(IF($C$2:$C$8=$F2,IF($D$2:$D$8=$G2,(LEN(UPPER($B$2:$B$8))-LEN(SUBSTITUTE(UPPER($B$2:$B$8),UPPER($E2),"")))/LEN(UPPER($E2)))))

Still array entered.

You're welcome!