Dynamic Programming Question Excel or MS Access

[anich22]

I am stuck. I've created an MS Access db to pull all invoiced and quote dollars for a specific brand from April 2013 to current. My required fields are sales manager and customer name. How do I set a criteria that will aggregate the customer sales dollars greater than or equal to $75K. Then I would like to count the number of customers by sales manager that met this minimum threshold.

As a concellation prize, I created the database to generate all sales for a specific brand, I summarized the data into a pivot table where sales manager and customer name is in my row field (I added a custom filter to show only those customers that have a subtotal greater than or equal to $75K)

I would like to identify the total number of customers by sales manager that meet the $75K threshold.

I really need help with this one, and I hope my question make sense.

Thank you in advance.

 

[Scuderia]

Hi

Create Access DB "Query1" with 2 fields only - "Customer" & "Dollar Amount" to sum Customer sales dollars. Add criteria >= $75k

Create Access DB "Query2" With DB "Query1" as one table. 2nd table is a table of "Customer by sales Mgr" (Unique records only)....... Linked to "Query1" by customer

Excel MSquery / External data connect to "Query2" to create your pivot table

Pivot table Row Field - "Sales Manager"
Pivot table Value as "count of customer"

Edit:...
That is if you want your output in a pivot table.
If you just require the data then use Query2 to Group by sales Manager & "Count of Customer"

 

[anich22]

Oh My Goodness, that worked. Thank you so much. So simple... I have been searching trying count, countifs, sumproduct, etc.... lol

 

[anich22]

Hi

Create Access DB "Query1" with 2 fields only - "Customer" & "Dollar Amount" to sum Customer sales dollars. Add criteria >= $75k

Create Access DB "Query2" With DB "Query1" as one table. 2nd table is a table of "Customer by sales Mgr" (Unique records only)....... Linked to "Query1" by customer

Excel MSquery / External data connect to "Query2" to create your pivot table

Pivot table Row Field - "Sales Manager"
Pivot table Value as "count of customer"

Edit:...
That is if you want your output in a pivot table.
If you just require the data then use Query2 to Group by sales Manager & "Count of Customer"

Oh My Goodness, that worked. Thank you so much. So simple... I have been searching trying count, countifs, sumproduct, etc.... lol​

 

[Scuderia]

OK...