( by Stupac310 )
I have a list of items sold by various sales reps over a 24-month period. I have sales dates and in some cases cancellation dates. I would like to count the number of sales each particular rep had in each month. I would also like to count how many of those sales wound up as cancellations. (Browser: Chrome).
INPUT: Columns are (A) Item Sold; (B) Sales Rep; (C) Sales Date as xx/xx/xxxx; (D) Cancellation Date as xx/xx/xxxx.
item sold Jane 12/15/2012 06/10/2013
item sold Fred 01/04/2013 03/25/2013
item sold Jane 01/20/2013
DESIRED OUTPUT: Columns would be (A) Sales Rep; (B, C, etc.) Month-Year; (Sub1) Sales ; (Sub2) Cancellations.
Dec-2012 Jan-2013
Sales Cans Sales Cans
Fred 0 0 1 1
Jane 1 1 1 0
What formula do I put in these cells? Count/Filter formulas? Array formulas? Thanks for any insight.
Solution:
Have a look at the following screenshot of my Spreadsheet:
I have the following formula in Cell F1 (colored as yellow in the above screenshot):
=query(VMerge(arrayformula(if(C:C="";"";if({1,0,0,0};B:B;if({0,1,0,0};"Sales";if({0,0,1,0};1;text(C:C;"mm-yyyy"))))));arrayformula(if(D:D="";"";if({1,0,0,0};B:B;if({0,1,0,0};"Cans";if({0,0,1,0};1;text(D:D;"mm-yyyy")))))));"select Col1,sum(Col3) where Col1<>'Sales Rep' and Col1<>'' group by Col1 pivot Col4,Col2 ")
the format of the above formula's result is not the exact output as desired by you, but it is a single formula solution.
And if you want the exact desired output as per your requirement then have a look at this link:
http://igoogledrive.blogspot.com/2013/07/filtering-and-formatting-data.html
I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.
I also take up private and confidential projects:
If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html
No comments:
Post a Comment