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

AnyaK

New Member
Joined
Jun 5, 2017
Messages
32
Office Version
  1. 365
Platform
  1. Windows
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.)
 
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"))
 
Upvote 0

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
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
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top