In excel how can I return the number of unique values in one column based on criteria in another column

Question

I have a set of data that includes Sku Value in Column A and Locations in Column C, but locations are duplicated in the data. I'm trying to create a formula that will let me enter a Sku into cell H2 and returns the number of unique locations in column C that have a value in column A matching H2.

Sku values are alphanumeric and also contain a / (i.e. ABCD123/A) and Locations are alphanumeric (i.e. 123012A01)

The number of rows of data will also potentially change so I'm trying to get it to work for any number of rows. I do not have access to the UNIQUE function in my version of excel.

Example of what finished sheet would look like

question from:https://stackoverflow.com/questions/65948462/in-excel-how-can-i-return-the-number-of-unique-values-in-one-column-based-on-cri

Answer 1

If you have access to the new excel formulas FILTER & UNIQUE then:

=COUNTA(UNIQUE(FILTER(B2:B7,A2:A7=D2)))

---

If you don't have access to those formulas then you can use an array formula which must be confirmed with CTRL + SHIFT + ENTER

=SUM(--(FREQUENCY(IF(A2:A7=D3,MATCH(B2:B7,B2:B7,0)),ROW(B2:B7)-ROW(B2)+1)>0))

---

Image to relate equations to relevant ranges