LinkBack URL
About LinkBacks
Hi
Im trying to work out the formula to return the sales for a specified department for a specified month. I want to be able change the month and department to get results from past months a well.
Im stumped...
Hi
Im trying to work out the formula to return the sales for a specified department for a specified month. I want to be able change the month and department to get results from past months a well.
Im stumped...
Like so...
The formula in B10 is an array formula, any changes you make, confirm them by pressing CTRL-SHIFT-ENTER to keep the array active, then copy the cell downward.
The rest of the formulas are standard INDEX/MATCH formulas.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
Hi Chanley,
welcome to the forum.
Instead of the report-type format, re-arrange your data in a simple table and then create a pivot table with filters for the date and type.
see attached.
Hi Guys
Thanks for your help. Your answers have prompted me to go one step further.
What if i wanted to try and get the Cost of Goods Sold (COGS) figure as well? So i would now require the Sales figure and COGS figure for the specified month and department? I played around with the formulas and ideas you presented but couldnt work it out.
Add another column in the data entry table for COGS and enter the COGS figure for each line. Then pull that into the pivot table.
(this is based on the file I posted earlier, not the one in your last post)
I would strongly recommend that you use the PivotTable & avoid Array Formulas which can be slow to calculate
Hope that helps.
RoyUK
--------
For Excel Tips & Solutions, free examples and tutorials why not check out my web site
Free DataBaseForm example
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks