Using VBA to create a Sales Summary

Good Day,

I want to create a sales summary report with VBA.  Using a simple example, , say I have a table with 3 columns: Fruit, Sales, Year
FRUIT      sales      Yr
Apples      400.06      2005
Apples      560.61      2004
Apples      952.38      2005
Apples      271.07      2003
Banana      630.28      2004
Banana      120.96      2003
Pear      401.17      2005
Pear      354.66      2004

Using an input box, I want  to enter the name of the fruit that I am looking for, the year that I am looking for and then  have code then sum the sales for the fruit and the year. I have attached a sample file with code so far.

Thanks ! :)

Initially, the output can be in a message box( I can move it to a range in the worksheet later).  Please advise.
Wm Allen SmithAsked:
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.

regmigrantCommented:
There's no attachment, but have you considered that a pivot table would provide that function without VBA?
Wm Allen SmithAuthor Commented:
Oops!! Here is the attachment. I actually figured it out by  using a sumifs formula. Is there a better way?
I don't want to use a pivot table because  I am actually going to adapt this to use with a large sales spreadsheet file with multiple  elements to produce individual summary reports based on company, year, region and sales year.
Sales_Sum_Test.xlsm
regmigrantCommented:
Sumifs is a very efficient way of doing it but the code overall seems like overkill since you can just put the formula in and whenever you change the fruit/year it gets recalculated. You could put also put the sumif function itself into a named range and refer to it with = <currentfruityear> (or whatever you call it).

Presumably its like this to support the bigger project but without knowing the detail of that there's not much I can comment on!

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
Become a Microsoft Certified Solutions Expert

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Based on what you already have, you only need this....
Sub findfruitsales()
Dim fruitName As String
Dim Yr As String
fruitName = InputBox("Enter fruit")
Yr = InputBox("Enter Year")
With Sheet1
    .Names.Add Name:="fruitNameRng", RefersTo:=.Range("G2")
    .Names.Add Name:="yrNameRng", RefersTo:=.Range("G3")
End With
MsgBox Round(WorksheetFunction.SumIfs(Range("slsRng"), Range("frtRng"), Range("fruitNameRng"), Range("yrRng"), Range("yrNameRng")), 2)
End Sub

Open in new window

Wm Allen SmithAuthor Commented:
Thanks Skinner--that works!!!
Wm Allen SmithAuthor Commented:
I mean "skinner". I love autocorrect ;)
regmigrantCommented:
You're welcome, if you decided to use Sktneer code you should split the points
Wm Allen SmithAuthor Commented:
Hi Reg,

How do I split points?

AN interesting twist on the problem: How would I configure the code to show the topN fruit sales for a given year?

Thanks!
regmigrantCommented:
Rrefer the question to moderator and indicate what split you want to give

The follow up question needs to be a fresh post to get most eyes on it - anything marked as complete will be ignored
Wm Allen SmithAuthor Commented:
Ok thx Reg. Very helpful! :D
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.