Complex Unique Count - Is This Possible?

AndyPandy27

Board Regular
Joined
Jul 17, 2012
Messages
142
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):

NameAccountItemGroupSales
Person AAccount 1Item 1Group 1$1,500
Person AAccount 2Item 1Group 1$500
Person AAccount 1Item 2Group 2$500
Person AAccount 2Item 2Group 2$500
Person AAccount 3Item 1Group 1$2,500
Person AAccount 1Item 3Group 1$1,500
Person AAccount 2Item 3Group 1$2,500
Person BAccount 4Item 1Group 1$500
Person BAccount 5Item 1Group 1$700
Person BAccount 4Item 2Group 2$500
Person BAccount 5Item 2Group 2$500
Person BAccount 4Item 3Group 1$500
Person BAccount 4Item 4Group 2$2,500
Person BAccount 5Item 1Group 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
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Hi Andy

There are ways to do this in a single cell but they are quite complicated, and I think it might be easier if you have a separate tab for aggregation at each of the levels, using either formulas or a pivot table. For example, you could have a pivot table that filtered by Name, Account and Group, then count from this aggregated data how many for each Name/Acc/Grp combination summed to 2000 or more.

An alternative would be a helper column that was a SUMIFS() based on your criteria (e.g. Sum on Sales while NameArray=Name, GroupArray=Group) but only appeared for the first time that particular criteria was met, i.e. COUNTIFS() on the same criteria but from the top of the table to your current row =1. So for your data pasted into A1 (and with the Sales values changed to numbers rather than text), aggregating based on Account and Name:

Code:
=IF(COUNTIFS($A$2:A2,RequiredPerson,$B$2:B2,B2)=1,SUMIFS(E$2:E$15,A$2:A$15,RequiredPerson,B$2:B$15,B2),"")

You could then trivially count how many of these were 2000 or more.

Hope that helps

Mackers
 
Last edited:
Upvote 0
Hi Aladin,

Thank you for getting back in touch.

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

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).

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

AP
 
Upvote 0
Try this
Sheet1 Data
Sheet2 A2=Name B2=Group C2=Conditional Amount

Code:
=SUMPRODUCT((Sheet1!$A$2:$A$15=Sheet2!A2)*(Sheet1!$D$2:$D$15 =Sheet2!B2)*(Sheet1!$E$2:$E$15>Sheet2!C2)*(Sheet1!$E$2:$E$15))
 
Upvote 0
Try Sumproduct something like this

=sumproduct((A:A="Person A")*(B:B="Account 2"))

This will give you the number of times Person A is in the first column and Account 2 is in the second, but only if they are both present. You can define more criteria if you want by multiplying another criteria.

=sumproduct(((A:A="Person A")*(B:B="Account 2")*(E:E>2000)))

Basically it goes across the sheet and if all criteria are met it adds 1, if only 1 or less criteria is not met it will not count at all.
 
Upvote 0
@JokelesComedian

Though the use of entire column references within SUMPRODUCT is not at all to be recommended.

Regards
 
Upvote 0
I had no idea, I haven't tried it on a large scale is probably why I didn't have issues. I will keep that in mind, thanks.
 
Upvote 0
I don't think these Sumproduct solutions are going to do what he is asking for. So far, I think that Mackers is going in the right direction talking about using Pivot Tables. But even with the Pivot tables it comes down to doing a manual count... KSRINIV, your Sumproduct evaluates to 2. His desired outcome is 3.
 
Upvote 0
Hi All - thank you very much for the continued inputs - but I haven't yet found a working solution:

@Mackers - what you say makes complete sense; failing a one-cell solution, I'll be able to fairly easily calculate what I need using a helper column. Good shout, cheers.

@kvsrinivasamurthy - thanks for the suggestion, but this doesn't actually do what I need. This creates a total sales aggregation, I need a count of unique Accounts, whose total sales for a specific Group are >$2000.

@JokelesComedian - I think you had a similar idea to @kvsrinivasamurthy; your solution counts the number of times the individual rows in the Sales column are >$2000 when the Name is the same as the one selected by a User, and the Account belongs to that Name. This is *nearly* what I want; however, I need to aggregate the individual sales for each Account belonging to the selected Name (for Group 1), then count how many of those aggregations are >$2000.

Really appreciate everyone's help so far.

AP.
 
Upvote 0

Forum statistics

Threads
1,213,568
Messages
6,114,348
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top