Hello Excel Experts,
I am trying to cobble together a combo of a lookup and count of unique values within one formula.
I want to lookup the number of customers per store in a database and return that unique value (as represented by " Customer ID" in the Database worksheet/tab) in the presentation table as shown below (on worksheet 1). As you can see customer IDs are repeated per store since they are several repeat sales by customer ID so I want a unique value count as opposed to a total count.
I know a pivot table can do this from the info found in worksheet/tab #2 but I'm already utilizing a pivot table to summarize the data and other values found in the presentation table on worksheet/tab 1. To get the unique count of customer id's I've utilized SUMPRODUCT(1/COUNTIF(B:B,B:B)) in worksheet 2, but the tricky part is trying to embed some sort of lookup or Index/Match formula to find the unique customer ID count and return that value in the presentation table on Worksheet 1.
Thanks in advance for any advice offered.
Worksheet 1 Worksheet 2
Presentation Table
Column A Column B Column A Column B
Store # of customers/store Store Number Customer ID
100 3 100 1001
200 2 100 1002
100 1003
100 1001
200 2001
200 2001
200 2002