Count If Meets Multiple Criteria and Unique

Question

I have read several other relatable questions with responses, including referenced suggestions, but they do not appear to be similar in my requirements or I'm having troubles understanding what they're asking.

I have a database of companies, along with their respective contact information for their personnel. Each personnel contact has their own row and therefore a company may be listed several times if they have multiple contacts. I have about 6,000 contacts altogether that I track.

I send out notices to select contacts and use the database to keep track of results. The companies and their relative contacts are listed by a CSI Code depending on the services they provide.

This part of the database looks like this;

I need to count the number of unique companies that have an entry of 'Yes' for Column F, match the CSI # of 024119, and have an "a" in Column K. The criteria for Columns F, J, and K are simple but I don't know how to only count the company once if it has multiple entries.

In the screenshot, 'AAR Incorporated' should only be counted 1x instead of 3x.

Using this formula, I'm able to count the number of unique companies that match the same CSI Code but that is it.

=SUM(--(LEN(UNIQUE(FILTER($L$8:$L$10000,$J$8:$J$10000=J87,"")))>0))

Cell J87 contains the title block number of 024119.

I don't know how to introduce other criteria like a COUNTIFS formula for Columns F and K.

With the data in the screen shot, the formula should return a result of 1 for AAR Incorporated is the only company that has a 'Yes' in Column F, matches 024119 in Column J, and has an 'a' next to their name.

Answer 1

I assume 024119 in column J is text ant not numeric value.

=IFERROR(ROWS(UNIQUE(FILTER($L$8:$L$10000,($J$8:$J$10000="024119")*($K$8:$K$10000="a")*($F$8:$F$10000="yes")))),0)

Result:

You can multiply FILTER criteria. filter