more-arw search

Q&A Forum

Excel question concerning a combo of possible Vlookup & Count If Formulas

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

237 views

Get Free Membership

By signing up, you will receive emails from Proformative regarding Proformative programs, events, community news and activity. You can withdraw your consent at any time. Contact Us.

Business Exchange

Browse the Business Exchange to find information, resources and peer reviews to help you select the right solution for your business.

Learn more

Contribute to Community

If you’re interested in learning more about contributing to your Proformative community, we have many ways for you to get involved. Please email content@proformative.com to learn more about becoming a speaker or contributing to the blogs/Q&A Forum.