Counting any cell containing either all, one or a subset of, a list of words

[AnyaK]

Hi All,

Forgive me for the following text explanation but we are blocked from uploading items at work and I’m at work at present.

I have a basic table for recording health and safety, currently spanning A1 to N46.

Column C contains words that relate to the type of health and safety issue (e.g. Abuse, Accident, Near Miss, Violence, etc.)

For counting the number of Accidents, I can just use:

=COUNTIF(C:C,"*Accident*")


However, for counting number of instances of aggression, I need to count any cell containing either all, one, or a subset of, the following words:

• Abuse
• Harassment
• Intimidation

So far, I’ve failed miserably. I’ve tried:

=COUNT(SEARCH({"Abuse","Harassment","Intimidation"},C:C))
Which results in number 59, when my table only has 46 rows!

=(COUNT(ROWS(OR(ISNUMBER(SEARCH({"Abuse","Harassment","Intimidation"},C:C)))),A:N))
Just seems to count the number of rows minus the header row

=SUMIF(C:C,OR(ISNUMBER(SEARCH({"Abuse","Harassment","Intimidation"},C:C))))
Results in zero. (Although admittedly I’d lost the plot by this point.)

I will try to upload an image of the table tonight or tomorrow, as I can only do this outside of work, really sorry.
If anyone can help, I’d be really grateful.

Thank you.

(Apologies as usual for my idiocy, I am autistic with learning difficulties. Thank you for your patience.)

 

[Rick Rothstein]

Is there a way I can adapt your formula so that instead of counting any combination of "Abuse", "Harassment", "Intimidation" in C:C, it will only count these combinations where M:M also has the word "Eden" in the same row?

Give this modification of Marcelo's formula a try...

=SUMPRODUCT(--(MMULT(--ISNUMBER(SEARCH({"Abuse","Harassment","Intimidation"},C2:C10)),{1;1;1})>0),--(M2:M10="Eden"))

 

[AnyaK]

Give this modification of Marcelo's formula a try...

=SUMPRODUCT(--(MMULT(--ISNUMBER(SEARCH({"Abuse","Harassment","Intimidation"},C2:C10)),{1;1;1})>0),--(M2:M10="Eden"))

Many thanks Rick, it works perfectly. I changed the "Eden" to a cell reference in the end, just to save me having to (re)type the various other words I was looking to count in M:M besides "Eden", but it still worked perfectly.

Thanks so much for you help, time and patience.

AnyaK