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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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 PrewIT / Software Engineering ConsultantCommented:
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
Joel BuhrPresidentAuthor Commented:
Isn't there a way to do it without pivot tables and do it as formulas.
Bill PrewIT / Software Engineering ConsultantCommented:
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Get Your eBook
Joel BuhrPresidentAuthor Commented:
See if this works.
Workbook2.xlsx
Bill PrewIT / Software Engineering ConsultantCommented:
See if this works for you.

Workbook2.xlsx

»bp
Joel BuhrPresidentAuthor Commented:
Cool checking this out and will let you know.
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?
Bill PrewIT / Software Engineering ConsultantCommented:
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

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