Count distinct text values from column with specific conditions within that cell

[Fishboy]

Hi guys, long time no post!

I am currently helping someone who is using Excel to outline the schedule for multiple users across a month, and then using this to count and track workload and types of jobs carried out.

KEY POINT - I Cannot use any VBA solutions as macro enabled workbooks are not allowed

As per the attached image, columns A and B cover the dates throughout the month going downwards.

Column C shows the tester's schedule. An engagement which lasts more than one day has been merged into a single cell (I know, merged cells are a nightmare!)

Column E is a helper column which is usually hidden, but effectively it works out the value of the adjacent cell for counting purposes as the merged cells otherwise only count as 1. Dont think they will be useful for this task, but they are there if needed and I use them for a separate counting function anyways.

I need to be able to count the distinct values from either column C or E. Basically I am being asked to count how many distinct instances there are of cells containing (LHE), (LFE) or (SFE) within them, separated into the ones which also contain "Testing" or "CE Plus". They need to be distinct as sometimes (as per the example) "MEMBER 1 Testing (R) (LHE)" occurs more than once in the month and should only be counted once.

I had been using variations of the following formula depending on what I was counting, but these count the duplicates as well.
=COUNTIFS($C$6:$C$36,"*Testing*",$C$6:$C$36,"*(LFE)*")

I had also tried using this to count the distinct values, but I have not found a way to incorporate the additional conditions for "Testing" and the (LHE) criteria for example
{=SUM(IF(LEN(E6:E36),1/COUNTIF(E6:E36,E6:E36)))}

Can anyone offer any suggestions that may assist, or am I just making things too complicated?

Thanks in advance,

Fishboy!

 

[Fishboy]

Thanks again Dante. I will get a better example to share with you using the XL2BB tool and come back to you. Might be today, might not be until tomorrow. Appreciate your assistance.