excel autosum for filtered results

I have an excel file where the data is in the following format.

account ID, name of business, country (including empty cells where country info is missing), monthly revenue, url of the account.

I need to find the total monthly revenue for all the accounts in each country. There are about 100 countries there and more than 6000 accounts and so it is not easy or quick to do it manually.

How do I do this?

I am using a MAC by the way. I would also like to be able to do this in Google docs / spreadsheet.
smuralisankarAsked:
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.

Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Do a PivotTable - that's the quickest way.

You can create a PivotTable in Google spreadsheet as well.
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
smuralisankarAuthor Commented:
How do I get the result I want using a pivot table in this case?
0
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Country in the rows.
Month (which you presumably have, because otherwise "monthly revenue" would not make sense) in the columns.
Sum of Monthly revenue as the values.
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

smuralisankarAuthor Commented:
We don't have month as a field. These are people who are paying for a monthly subscription for SAAS product. So, we just have CMRR (monthly revenue).
0
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
OK, so Country in the rows, Sum of Monthly revenue as the values then.
0
smuralisankarAuthor Commented:
Thanks.

I would also like to know how to do this using another method (maybe use a formula).
0
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
In Excel, you can use Data - Remove Duplicates to generate the list, and then a SUMIF to generate the financial results.
0
Rob HensonFinance AnalystCommented:
As usual there are many ways. You could also use the Subtotal function to insert rows with subtotal sums when the data criteria specified changes, assumes data is sorted by the specified criteria.

Thanks
Rob H
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.