Count occurrences that contain one or more named range values

[RelativeRisk]

Hello, I'm working on a customer service log where five different people input their initials when they respond to an incident. I need to count the total number of times someone on a specific list responds to an incident. I put those five people in a named range and use the formula =SUMPRODUCT(COUNTIF(searchrange,namedrange)). It worked like a charm, but I now see that sometimes two people will respond to an incident and put a slash in the log instead of just one person's initials.

For example, if AB and CD both respond to something, they'll input AB/CD. My formula will not include that cell in its count, and I end up having a lower total than I should.

Any advice other than including all possible combinations of initials in the named range?

Thanks!

[63falcondude]

Attach a sample workbook (not a picture or pasted copy).

Make sure there is just enough data to demonstrate your need.

Make sure your desired results are shown, mock them up manually if necessary.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[AliGW]

Best advice would to prevent them from doing this. Maybe create drop-down lists for data entry and limit the cell’s content to what’s available. Don’t let the tail wag the dog!

[Zer0Cool]

I would agree with @AliGW.

Force users to enter consistent entries instead of trying to handle every hoop they can make you jump through.

I would use data validation as a drop down if: you can pull entries from a list (sounds like you could) and will only ever enter 1 entry at a time. You could use additional columns for multiple entries, but then need to adjust your formula to account for this.

The other option is to use data validation to limit what gets entered following some "rules". This could be something like length of the entry (2 characters long only) or not allowing special characters like /, \, | for example.

It sounds to me like you need to adjust your form/file to allow for a "proper" entry that accounts for more than 1 person being involved, unless you dont want more than 1 person per incident. The users have put entries using a / in there as the current form doesnt have any other way for them to indicate more than 1 person being involved. If you want them to be able to, provide them a way thats conducive to how you analyze the information post entry.

In combination with this, providing clear instruction for users will make everyone's job easier. This can be done using text boxes, comments in cells, data validation input/error messages, even sending out an email or providing a sheet with instructions in the file. Make them aware of the proper ways to fill in the file and how to go about it.