5

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).

sample data

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.

5

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. – Nick5a1 Nov 4 '13 at 19:24
  • @Nick5a1 That's because somebody else changed A9 to Doctor – Jacob Jan Tuinstra Nov 4 '13 at 19:27
  • @Nick5a1 Counting the occurrences, makes me end up with 5 instances and not 4 !! – Jacob Jan Tuinstra Nov 4 '13 at 19:28
  • @Nick5a1 None blank E......will change..... – Jacob Jan Tuinstra Nov 4 '13 at 19:30
  • @Nick5a1 Result is 4. – Jacob Jan Tuinstra Nov 4 '13 at 19:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.