Thread: Count Unique Customers by Class and then Consolidated

1. Count Unique Customers by Class and then Consolidated

In column A i have the names of customers separated by contract types. Some customers are listed in more than one contract type. For example, column A, rows 1-35 contain the names of customers having contract type I. In rows 36-78, the names of customers having contract type II, some of these latter customers also appear in rows 1-35. Finally, in rows 79-140, the names of customers having contract type III, again, some of these may also appear (more than once) in rows 1-78. In columns B-M, rows 1-140 are the monthly quantity of a commodity purchases by the respective sustomer under each contract type.

Ehat I would like is in row 142, columns B-M is list the number of "unique" (i. e., no duplicates) customers purchasing under contract type I. In row 143, columns B-M list the number of "unique" (i. e., no duplicates) customers purchasing under contract type II, and the same for contract type II in the same columns in row 144. Finally, in row 145, columns B-M list the number of truly unique customers doing business each month regardless of contract type. For example, if a customer in a given month purchased goods under contract types I and II, he would only be counted once.

Any ideas?

2. A pivot table might work. An example workbook would be helpful as well for us to play...

Steve

3. Here you go Steve

4. Is this what you're looking for? =COUNTIFS(\$B\$1:\$B\$50,"I",C\$1:C\$50,"<>0")

(change the 50 to however many rows you have of the three types, and change the "I" to "II" and then "III" in the two rows that are to count those types.)

Then, fill across to column M.

Oops, guess not...I reread your post and you're looking for unique customer counts.
As Steve said, maybe a pivot table is best. Rethinking.

5. Thanks for your reply, Kweaver. I think we almost have it, except I am looking for a "unique" counting of customers in each classification. Your formula includes duplicates in its results. ANy other ideas?

6. See the answer where you cross-posted. http://eileenslounge.com/viewtopic.p...410d2494295af9