Find Key Word or Phrase Between 2 Columns & Count or Sum Up Instances

Hi,

I'm seeking a key word or phrase out of 2 columns & count or total the instances only if both criteria has been ment.

I'm trying to use these function but they're not fully functional

=COUNTIF(OR($E$15:$E$1000,"*Open*",$G$15:$G$1000,"*Asset*"))


With this one, it works in some cells but not all cells - Why is this?
=SUMPRODUCT(--($E$15:$E$1000="Open"),--($G$15:$G$1000="Asset"))

More importantly, requesting assistance to get one of these working or for somone to suggest another type of function.

Thanks

Maybe like this

=SUM(COUNTIF($E$15:$E$1000,"Open"),COUNTIF($G$15:$G$1000,"Asset"))

The COUNTIF is checking if the test string is contained anywhere in each cell in the range.

The SUMPRODUCT is counting only the cells that exactly match the test string.

So if for example,
E20 = "open sesame"
then the COUNTIF would increment but the SUMPRODUCT would not.

Or rather, it would if you didn't incorrectly nest the OR in the COUNTIF.

Use COUNTIFS instead.

Please Login or Register  to view this content.

Hi Group,

Thanks for the assistance.

I think I also found a solution to my own problem with: =COUNTIF(E15:E36, "=Open")-COUNTIF(G15:G37, "=*Asset*")

I can imagine at least four different ways that would deliver wrong answers compared to the requirement stated in OP but okay!

Hi Ben,

Examples given are working.


Thanks