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