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

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.

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 !!

• Hi @Jacobjantulnstra. Thanks for your answer. unfortunately both those forumlas give a value of 6, when the correct value would be 4. Rows 6, 7 and 8 should count as 1 value only, as they are all from the same company and have identical value for column A. Nov 4 '13 at 19:24
• @Nick5a1 That's because somebody else changed `A9` to `Doctor` Nov 4 '13 at 19:27
• @Nick5a1 Counting the occurrences, makes me end up with 5 instances and not 4 !! Nov 4 '13 at 19:28
• @Nick5a1 None blank E......will change..... Nov 4 '13 at 19:30
• @Nick5a1 Result is 4. Nov 4 '13 at 19:32