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.)

 

[mumps]

Try: =SUM(COUNTIFS(C:C,{"*Abuse*","*Harassment*","*Intimidation*"}))

This will work only if any cell does not contain more than one of the 3 words.

 

[AnyaK]

Try: =SUM(COUNTIFS(C:C,{"*Abuse*","*Harassment*","*Intimidation*"}))

This will work only if any cell does not contain more than one of the 3 words.

Hi Mumps,

Many thanks for your reply, much appreciated.

Yes, that formula works great!

(I have to confess that I do now have a follow up question regarding the relevant table, but I will wait to trouble the thread with this once I am able to post an image of the table. Just wanted to mention it so that I don’t accidentally cause offence by adding another post to this thread a bit later on.)

Thanks again, I really appreciate your time and patience,

AnyaK

 

[mumps]

You are very welcome.

 

[Marcelo Branco]

Maybe this...

[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][td="bgcolor: #DCE6F1"]

E​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

Text​

[/td][td][/td][td]

Result​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

Abuse​

[/td][td][/td][td]

6​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

Abuse, Intimidation​

[/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

Harassment, Abuse​

[/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

Blah, blah​

[/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td]

Abuse​

[/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

7​

[/td][td]

Blah, blah​

[/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

8​

[/td][td]

Harassment​

[/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

9​

[/td][td]

Blah, blah​

[/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

10​

[/td][td]

Abuse, Harassment,Intimidation​

[/td][td][/td][td][/td][/tr]
[/table]

Formula in E2
=SUMPRODUCT(--(MMULT(--ISNUMBER(SEARCH({"Abuse","Harassment","Intimidation"},C2:C10)),{1;1;1})>0))

M.

 

[AnyaK]

Maybe this...

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[TD="bgcolor: #DCE6F1"]

E​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Text​

[/TD]
[TD][/TD]
[TD]

Result​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

Abuse​

[/TD]
[TD][/TD]
[TD]

6​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

Abuse, Intimidation​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

Harassment, Abuse​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

Blah, blah​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD]

Abuse​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD]

Blah, blah​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

8​

[/TD]
[TD]

Harassment​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

9​

[/TD]
[TD]

Blah, blah​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

10​

[/TD]
[TD]

Abuse, Harassment,Intimidation​

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


Formula in E2
=SUMPRODUCT(--(MMULT(--ISNUMBER(SEARCH({"Abuse","Harassment","Intimidation"},C2:C10)),{1;1;1})>0))

M.

Hi Marcelo,

Many thanks for your reply.

This was going to be my next 'follow up' question and your solution works perfectly for this.

I'm amazed that you were able to decipher my rambling first post and predict my next follow up question! Thank you!

This solution will save me so much time.

Many thanks again,

AnyaK

 

[Rick Rothstein]

EDIT NOTE: I see there was a follow up formula from Marcelo while I was busy creating the UDF and description below. You should use his formula but i am leaving my write up below "for the record".

Hi Mumps,

Many thanks for your reply, much appreciated.

Yes, that formula works great!

Are you sure Mumps' formula works correctly for your data? I ask because you said this in your first message...

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

That makes it sound like you could have more than one of these words in the same cell. If that is the case, then Mumps formula will count each of those words giving you a higher count than I think you want. For example, if cells C1:C3 contained these words..

A1: Abuse
A2: Abuse, Harassment and Intimidation
A3: Harassment

Mumps formula will return a count of 5 whereas my understanding of your request is you would only want a count of 3. If I am correct, then here is a UDF which would return a count of 3 for the above example...

[table="width: 500"]
[tr]
	[td]Function CountOr(Rng As Range, ParamArray Words() As Variant) As Long
  Dim Cell As Range, W As Variant
  For Each Cell In Rng
    For Each W In Words
      If InStr(1, Cell.Value, W, vbTextCompare) Then
        CountOr = CountOr + 1
        Exit For
      End If
    Next
  Next
End Function[/td]
[/tr]
[/table]

This function's first argument is the range to perform the count in and that is followed by a comma delimited list of the words to count. So, if your data was in cells C1:C99, then you would use this formula to get the count of cells containing at least one of those words...

=CountOr(C1:C99,"Abuse","Harassment","Intimidation")


HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use CountOr just like it was a built-in Excel function. See example above.

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[Marcelo Branco]

Hi Marcelo,

Many thanks for your reply.

This was going to be my next 'follow up' question and your solution works perfectly for this.

I'm amazed that you were able to decipher my rambling first post and predict my next follow up question! Thank you!

This solution will save me so much time.

Many thanks again,

AnyaK

You are welcome. Glad to help

M.

 

[AnyaK]

Hi Rick,

Many thanks for your comprehensive reply.

I got myself in a bit of a jumble in my rambling first post. What I wanted was ideally both the result of yours and Marcelo's solution, but also the option the mumps kindly provided too.

Sorry, autism and good communication skills don't go well together (for me, anyway).

I've just tried your solution and it works perfectly, in the way that you both understood my ramble, and as you described, which is what I was looking for, and will save me a lot of time with this project and others.

Thanks again and so sorry to you (and everyone else who replied) for being confusing.

Thank you Rick,

AnyaK

 

[AnyaK]

Maybe this...

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[TD="bgcolor: #DCE6F1"]

E​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Text​

[/TD]
[TD][/TD]
[TD]

Result​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

Abuse​

[/TD]
[TD][/TD]
[TD]

6​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

Abuse, Intimidation​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

Harassment, Abuse​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

Blah, blah​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD]

Abuse​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD]

Blah, blah​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

8​

[/TD]
[TD]

Harassment​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

9​

[/TD]
[TD]

Blah, blah​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

10​

[/TD]
[TD]

Abuse, Harassment,Intimidation​

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


Formula in E2
=SUMPRODUCT(--(MMULT(--ISNUMBER(SEARCH({"Abuse","Harassment","Intimidation"},C2:C10)),{1;1;1})>0))

M.

Hi Marcelo,

Thanks again for your help.

Sorry to trouble you and others again.

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?

I can post examples this evening (we are not permitted to do this at work - it's completely blocked).

I've tried COUNTIFS and adding AND etc. but am getting "VALUE" errors or just zeros. Clearly I'm not very clever!

Sorry to trouble you and everyone again.

Many thanks,

AnyaK