Excel - Count Occurrences and Group By

Trying to figure out the formula.

We have a excel job sheet where we track orders from clients, so there is a column that has the client name and if they order more then once in a month of course that value is repeated multiple times, and we have one column that has a REP code (something like ABB, or MCC - its a sales rep code) and then we have another column that has either a value of R or N this indicates if it was a New client or a Repeat client.

Here is what I am wanting to calculate on each months job sheet.  How many New Customers by Rep, and How Many Repeat customers. Also too how many jobs or orders has that sales rep placed that month. Keep in mind that we have a sheet for each month.

Columns are like the below.

JOB      Contact | Customer | Sale      Cost (QB) | Margin | Profit % | New/Repeat | REP
Joel BuhrPresidentAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bill PrewCommented:
Sounds like a Pivot Table to me.  Should multiple jobs for the same customer count once each month, or multiple times?

It would be helpful if you could upload a workbook with an existing data sheet, and then an example of the output and format you want.

»bp
1
Joel BuhrPresidentAuthor Commented:
Isn't there a way to do it without pivot tables and do it as formulas.
0
Bill PrewCommented:
It would be helpful if you could upload a workbook with an existing data sheet, and then an example of the output and format you want.

»bp
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Download your Guide
Joel BuhrPresidentAuthor Commented:
See if this works.
Workbook2.xlsx
0
Bill PrewCommented:
See if this works for you.

Workbook2.xlsx

»bp
0
Joel BuhrPresidentAuthor Commented:
Cool checking this out and will let you know.
0
Joel BuhrPresidentAuthor Commented:
Question, is there a way to also add a column that counts how many Unique Repeat Clients? So count how many have an R and only count one occurrence of the Company Name?
0
Bill PrewCommented:
Here is an approach for that.  Not that these are array formulas, so when you edit them and enter them you have to press CTRL-SHIFT-ENTER together.  Notice the formulas show in the formula bar with curly braces indicating they were entered as array formulas.

Workbook2.xlsx

»bp
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Joel BuhrPresidentAuthor Commented:
Wonderful help works great!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.