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

 

[Aladin Akyurek]

Hi Aladin,

Thank you for getting back in touch.

The outcome for Person A, where Sales > 2000 would be 3:



Let me know if you need anything else - thanks again!

AP

2000 must hold for the sales totals, not for the individual sales, right?

 

[AndyPandy27]

Hi Aladin,

2000 must hold for the sales totals, not for the individual sales, right?

That is correct.

Thanks,

AP

 

[Aladin Akyurek]

Hi Aladin,



That is correct.

Thanks,

AP

~

Row\Col	A​	B​	C​	D​	E​	F​	G​
1​	Name	Account	Item	Group	Sales		person A
2​	Person A	Account 1	Item 1	Group 1	$1,500​		2000​
3​	Person A	Account 2	Item 1	Group 1	$500​		3​
4​	Person A	Account 1	Item 2	Group 2	$500​
5​	Person A	Account 2	Item 2	Group 2	$500​
6​	Person A	Account 3	Item 1	Group 1	$2,500​
7​	Person A	Account 1	Item 3	Group 1	$1,500​
8​	Person A	Account 2	Item 3	Group 1	$2,500​
9​	Person B	Account 4	Item 1	Group 1	$500​
10​	Person B	Account 5	Item 1	Group 1	$700​
11​	Person B	Account 4	Item 2	Group 2	$500​
12​	Person B	Account 5	Item 2	Group 2	$500​
13​	Person B	Account 4	Item 3	Group 1	$500​
14​	Person B	Account 4	Item 4	Group 2	$2,500​
15​	Person B	Account 5	Item 1	Group 1	$1,500​

In G3 control+shift+enter, not just enter:

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

<strike></strike>

 

[lrobbo314]

WOW! I was thinking something with Frequency, but I am not as familiar with it. Aladin, that is awesome. I knew you would figure it out.

 

[AndyPandy27]

Hi Aladin,

This is an awesome formula - thank you so much for taking the time; however, it does not take into consideration the Group element, which is part of the criteria.

This is *so* close; I just need to ensure that the relevant Group (in this case Group 1), is also considered within the calculation.

The steps would be:

1. Create a sum of just the Group 1 sales for each Account, for the chosen Name.
2. Count the number of the sums from Step 1 (above), which are >$2000.

So using the above table for Person A and Group 1, you would get the following totals for each Account belonging to Person A (this would be Step 1):

£3,000 (for Account 1); £3,000 (for Account 2); £2,500 (for Account 3)

Then, you would run through these totals, and count how many were >$2000 (Step 2) - which gives you the answer we're looking for: 3

Can that be done in a single formula?

Thanks again.

 

[XOR LX]

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

<strike></strike>

I think the logic is flawed here, since one of the conditional statements (IF($A$2:$A$15=G$1) is outside the SUMIFS. For example, for this dataset:

Row\Col	A​	B​	C​	D​	E​	F​	G​
1​	Name	Account	Item	Group	Sales		person A
2​	Person A	Account 2	Item 1	Group 2	$1,500​		2000​
3​	Person B	Account 5	Item 1	Group 1	$500​		4​
4​	Person B	Account 3	Item 2	Group 1	$500​
5​	Person B	Account 4	Item 2	Group 1	$500​
6​	Person B	Account 5	Item 1	Group 2	$2,500​
7​	Person B	Account 5	Item 3	Group 2	$1,500​
8​	Person B	Account 3	Item 3	Group 1	$2,500​
9​	Person A	Account 5	Item 1	Group 2	$500​
10​	Person A	Account 3	Item 1	Group 2	$700​
11​	Person A	Account 3	Item 2	Group 1	$500​
12​	Person A	Account 1	Item 2	Group 1	$500​
13​	Person B	Account 4	Item 3	Group 2	$500​
14​	Person B	Account 4	Item 4	Group 1	$2,500​
15​	Person B	Account 5	Item 1	Group 1	$1,500​

<tbody>
</tbody>

your formula returns 4, though the correct result, I believe, should be 0.

Regards

 

[XOR LX]

Hi.

With your table in A1:E15 (with headers in row 1), your person (e.g. "Person A") in G1, your group (e.g. "Group 1") in H1 and your value (e.g. 2000) in I1, array formula**:

=SUM(IF(FREQUENCY(IF(SUMIFS(E2:E15,A2:A15,G1,B2:B15,B2:B15,D2:D15,H1)>I1,MATCH(B2:B15,B2:B15,0)),ROW(B2:B15)-MIN(ROW(B2:B15))+1),1))

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

 

[Aladin Akyurek]

Hi Aladin,

This is an awesome formula - thank you so much for taking the time; however, it does not take into consideration the Group element, which is part of the criteria.

This is *so* close; I just need to ensure that the relevant Group (in this case Group 1), is also considered within the calculation.

The steps would be:

1. Create a sum of just the Group 1 sales for each Account, for the chosen Name.
2. Count the number of the sums from Step 1 (above), which are >$2000.

So using the above table for Person A and Group 1, you would get the following totals for each Account belonging to Person A (this would be Step 1):

£3,000 (for Account 1); £3,000 (for Account 2); £2,500 (for Account 3)

Then, you would run through these totals, and count how many were >$2000 (Step 2) - which gives you the answer we're looking for: 3

Can that be done in a single formula?

Thanks again.

Row\Col	A​	B​	C​	D​	E​	F​	G​
1​	Name	Account	Item	Group	Sales		person A
2​	Person A	Account 1	Item 1	Group 1	$1,500​		2000​
3​	Person A	Account 2	Item 1	Group 1	$500​		Group 1
4​	Person A	Account 1	Item 2	Group 2	$500​		3​
5​	Person A	Account 2	Item 2	Group 2	$500​
6​	Person A	Account 3	Item 1	Group 1	$2,500​
7​	Person A	Account 1	Item 3	Group 1	$1,500​
8​	Person A	Account 2	Item 3	Group 1	$2,500​
9​	Person B	Account 4	Item 1	Group 1	$500​
10​	Person B	Account 5	Item 1	Group 1	$700​
11​	Person B	Account 4	Item 2	Group 2	$500​
12​	Person B	Account 5	Item 2	Group 2	$500​
13​	Person B	Account 4	Item 3	Group 1	$500​
14​	Person B	Account 4	Item 4	Group 2	$2,500​
15​	Person B	Account 5	Item 1	Group 1	$1,500​

In G4 control+shift+enter, not just enter:

=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,$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))

<strike></strike>

 

[AndyPandy27]

Hi Guys,

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

@Aladin - thank you for getting back to me again (and so quickly). Your updated solution works perfectly for the original dataset, however, when I update any of the "input" values (e.g. the Group, or the cut-off value of $2000), the formula no longer returns the correct result...? I'm not sure if that is down to me or not, plus I appreciate I did not mention anything about these "input" values changing, so perhaps your solution does not take that into consideration. That being said, XOR LX's formula does continue to update the result to be correct even when certain elements change (e.g. when using the amended data table he (or she) posted, or if I were to change the cut-off value from $2000 to $400).

Regardless, I really appreciate all the help and the incredible responsiveness of everyone.

Thank you very much indeed,

AP

 

[Aladin Akyurek]

Hi Guys,


@Aladin - thank you for getting back to me again (and so quickly). Your updated solution works perfectly for the original dataset, however, when I update any of the "input" values (e.g. the Group, or the cut-off value of $2000), the formula no longer returns the correct result...? I'm not sure if that is down to me or not, plus I appreciate I did not mention anything about these "input" values changing, so perhaps your solution does not take that into consideration.[...]

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)