Using VBA to create a Sales Summary

Wm Allen Smith
Wm Allen Smith used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
There's no attachment, but have you considered that a pivot table would provide that function without VBA?

Author

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
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!
Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
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

Author

Commented:
Thanks Skinner--that works!!!

Author

Commented:
I mean "skinner". I love autocorrect ;)
You're welcome, if you decided to use Sktneer code you should split the points

Author

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

Author

Commented:
Ok thx Reg. Very helpful! :D

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial