Complex Unique Count - Is This Possible?

[AndyPandy27]

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

<tbody>
</tbody>

(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

 

[XOR LX]

@XOR LX - you were exactly right, and your solution is working flawlessly. Thank you very much indeed. Really neat formula - a fantastic help.

You're welcome!

...when using the amended data table he (or she) posted...

He, but full marks for the lack of presumption!

Cheers

 

[AndyPandy27]

I failed to update the SumIfs bit with the additional condition...

=SUM(IF(FREQUENCY(IF($A$2:$A$15=G$1,IF(1-($B$2:$B$15=""),IF($D$2:$D$15=G$3,IF(SUMIFS($E$2:$E$15,$D$2:$D$15,G$3,$A$2:$A$15,A2:A15)>G$2,MATCH($B$2:$B$15,$B$2:$B$15,0))))),ROW($B$2:$B$15)-ROW($B$2)+1),1)

Aha! All working now.

Thanks again - this is great.

AP

 

[Aladin Akyurek]

Aha! All working now.

Thanks again - this is great.

AP

And it should be robust, hence all those terms...