Excel: Complex Unique Count - Is This Possible?


Hi All, I'm ideally looking for a one-cell solution, happy with a formula or VBA function (it needs to be able to update as the background data updates though). I have a data set in the background similar to the below (assume "Name" is in Cell A1):

Name Account Item Group Sales
Person A Account 1 Item 1 Group 1 $1,500
Person A Account 2 Item 1 Group 1 $500
Person A Account 1 Item 2 Group 2 $500
Person A Account 2 Item 2 Group 2 $500
Person A Account 3 Item 1 Group 1 $2,500
Person A Account 1 Item 3 Group 1 $1,500
Person A Account 2 Item 3 Group 1 $2,500
Person B Account 4 Item 1 Group 1 $500
Person B Account 5 Item 1 Group 1 $700
Person B Account 4 Item 2 Group 2 $500
Person B Account 5 Item 2 Group 2 $500
Person B Account 4 Item 3 Group 1 $500
Person B Account 4 Item 4 Group 2 $2,500
Person B Account 5 Item 1 Group 1 $1,500
(The above would be updated on a monthly basis; it would remain in the same format, but the values and the number of rows would change.) There is then a separate worksheet, which contains a data validation drop-down, allowing a user to select a "Name". I need to be able to calculate the unique number of Accounts, linked to that selected Name, which have total sales for Group 1 >$2,000. Using the above example, if a user had selected Person A, the result would be: 3 (the 2 x orange font lines for Account 1, the 2 x blue font lines above for Account 2 and the 1 x red font line above for Account 3). Is there a way to perform the aggregation of sales at Name/Account/Group level, then calculate the number of those aggregations which meet a set criterion (in this case >$2,000)...? Any help greatly appreciated. AP


This question generated 23 answers. To proceed to the answers, click here.

This thread is current as of October 07, 2015.


For more resources for Microsoft Excel