monthly summary for items by sum cell values based on text and input totals in month year
I have a formula that matches up the month, but not for the year.
A sale on 08-March-2016 will appear in March for both 2016 and 2017. I have highlighted the problem cell in red in the below image. Is there a way to include year?
example4.jpg
I have also attached the file I am using to try and figure this out.
Item.xls
Hi,
You problem begs for a Pivot Table. You don't need any formulas to do Pivot Tables. See the attached where I've added two helper columns for Month and Year. Then filter by the Year and drag the Months to the Column Head in the Pivot Table. See the attached.
One test is worth a thousand opinions.
Click the * Add Reputation below to say thanks.
I am making this for someone who has never used pivot table before (and neither have I) so I would prefer a formula.
We need to keep adding orders with new item names with quantities, it would be easier for us to keep adding items to column A and then copying the new item name into Column G to give us the monthly order quantities as needed.
I realized that I attached an older excel file instead of the one shown in the image that I am currently using. Here is the correct excel file.
Items.xlsm
example4.jpg
Hi,
I've beefed up my example by adding more rows to your table. I'm sticking with Pivot Tables because they are so easy and robust. Learn a bit more about Pivots before you try to improve that Hard to understand and use SumProduct formula. Pivots will automatically add more fruit names so you don't need to add them manually.
If I'm the GURU and think you should learn how easy Pivots are and wonderful - why would you want the harder answer. I'm lazy too and a little Pivot understanding is needed to do a great job with helping your friend.
http://www.excel-easy.com/data-analy...ot-tables.html
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks