CODEWITHSUNDEEP
excelexcel-formula
ellipsis
ellipsis

Reputation: 11

using Excel functions to count unique values in one column based on match in other column?

I have an Excel question - is there a method of using functions to count all of the unique values of a column based on matching another column's contents with a particular data interest? For example, suppose I have two columns of data.

A           | B
toothbrush  | AAA
shampoo     | AAA
toothbrush  | AAA
toothbrush  | BBB
conditioner | CCC
toothbrush  | BBB
shampoo     | CCC
toothbrush  | CCC
toothpaste  | CCC
toothpaste  | AAA
toothbrush  | AAA
shampoo     | BBB

I would like to generate, on a separate tab, two columns that display the following information, for example:

C           | D
toothbrush  | 3
toothpaste  | 2
shampoo     | 3
conditioner | 1

Column D would be the number of unique customers for each of the 4 products.

Is there a way to do this in Column D using a formula composed of SUM, IF, COUNTIF, etc.?

Thanks in advance and please let me know if I can clarify anything further.

Upvotes: 0

Views: 3195

Answers (2)

DTS
DTS

Reputation: 423

For the list of unique items, this will do the trick (it will produce blank cells as well):

=IF(COUNTIF(Sheet1!$A$1:A1,Sheet1!A1)>1,"",Sheet1!A1)

Put =IF(COUNTIF(Sheet1!$B$1:B1,Sheet1!B1)>1,"",Sheet1!B1) into column E to produce unique customers.

Finally, put this into column D, making sure to extend it to accommodate the number of rows on sheet1 and pressing CTRL+SHIFT+ENTER to make it an array formula:

{=IF(A1="","",
SUM(IF(COUNTIFS(Sheet1!$A$1:$A$12,A1,Sheet1!$B$1:$B$12,$C$1:$C$12)>0,1,0)))}

Upvotes: 0

pnuts
pnuts

Reputation: 59442

Since others may not be so particular about what readily available tools they decide to avoid, create a PivotTable with "Add this data to the Data Model" with A for ROWS and B for VALUES, then change Count of B to Distinct Count of B.

SO25755209 example

Upvotes: 1

Related Questions