Count with multiple criteria but duplicates only once

I have the following table:

Column A - Equipment Number Column B - Level Column C-Work Column D - Type Column E - Status
11160085 C3 MECHANICS PREV OK
11160085 C3 ELECTRONICS PREV OK
11160085 B MECHANICS PREV OK
11160085 B ELECTRONICS PREV PEN
11071846 C MECHANICS CERT OK

I need to count every equipment that has status OK and type PREV. However the tricky part is that only if the equipment has status OK on both mechanics and electronics (column C) should it be counted once.
For example the count for the table above should be 1. Only equipment number 11160085 has OK for level C3 on both Mechanics and Electronics and both are type PREV. If I were to change row 2 to Status to Pen, then the count should be 0. If rows 1 through 4 had status OK, then the count should be 2.

I've tried to use SUM array formula {SUM((E2:E5="OK")*(D2:D5="PREV"))*(C2:C5="MECHANICS"))*SUM((E2:E5="OK")*(D2:D5="PREV"))*(C2:C5="ELECTRONICS"))} and it works fine if the table only had rows 1 and 2. Once I add rows 3 and 4 it does not count correctly.

Any help would be greatly appreciated.

Thanks.

However I cant use the level C3 as a counting criteria because I want the count to reflect a general compliance percentage.

What I can tell you is that no equipment has a repeated level and work.

For example 11160085 - C3 - MECHANICS - PREV would only be found once in the table data. That combination is unique.

On the other hand 11160085-C3-PREV would only be found twice in the table data.

Hope this info helps!!

The formula Im looking needs to work independent of the level criteria.

For example in the table you posted, if you change row 5 to status OK, the count should change to 2.

Because it counts with C3 criteria, it will not do so.

To make myself clear. This is how I want to count:

EXAMPLE.PNG

But it appears that the Level criteria is a 'pairing' mechanism - ie.
11160085 C3 MECHANICS PREV OK
11160085 C3 ELECTRONICS PREV OK
are a matched pair, and together equal a count of 1.
So surely in our solution, using level is a useful grouping criteria. Or am I misinterpreting this?

Here are some examples of how the count should change if I modified status column.

EXAMPLE.PNG

EXAMPLE-1.PNG

Cyangou,

The problem relies that I need a Division general count as shown by the images so that when the user manually modifies the status column, the division count increases or decreases. If I use the Level as a criteria I would need to take into account all possible levels A-B-C-D-B1-C1-C2-C3-C4-B1-B2-B3-B4 present in the Level array.

Ignoring your warnings about Level (mostly because I didn't understand them), I've used it in my pairing mechanism:

Count with multiple criteria but duplicates only once.xlsx

Please see if it works for you.

Cyangou,

It works fine if each level has only one pair of data. Once you add another pair it does not work.

EXAMPLE.PNG

NewDoverman,

I understand your confusion. I incorrectly put the same number of equipment in rows 1 through 4.

Take a look at the picture I just posted for cyangou. See if it clarifies your criteria confusion.

Ok, that changes things. A completely different approach:

Count with multiple criteria but duplicates only once2.xlsx

Cyangou,

You have just made my day.

Thanks!!!!!!!!

Have a look and see if this is what you are after

Example.xlsx