Count the number of non-blanks that have unique value in other column

Question

I have a large spreadsheet of customer data. I've set up a sample spreadsheet with some simple fake data. You can view the sample data (with edit privileges).

I need a formula to be able to count the number of companies that have been contacted. So it needs to count the non-blank cells in column E, but take into account if there are multiple contacts at the same company, evident by having the same value in column A.

In this sample data the formula would give a value of 4, as there are 6 non-blank cells in column E, but A6, A7 and A8 have the same value and should only count as 1.

Answer 1

I was able to create two options:

Formula 1

=COUNTUNIQUE(FILTER(A2:A11,E2:E11<>""))

or

=COUNTA(UNIQUE(FILTER(A2:A11,E2:E11<>"")))

Formula 2

=COUNTA(QUERY(A2:E11, "SELECT COUNT(E) WHERE E<>'' GROUP BY A label COUNT(E) '' "))

Explained

The first two formulas are identical. They filter column A, where column E has entries. Afterwhich column A is filtered for unique names. These names are than counted. The COUNTUNIQUE function combines the COUNTA and UNIQUE.

The second formula will count the number of occurrences per unique business name having an entry (<> '' and group by). The result is counted. Remove the COUNTA function to see the intermediate result.

Example

See you're own example file for the results !!