Google Spreadsheet Filtering and Formatting data

Question:

( 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 Sheet1 of my Spreadsheet:

Have a look at the following screenshot of Sheet2 of my Spreadsheet:

I have the following formula in Cell B3 of Sheet 2:

=ArrayFormula(if({1,0};counta(iferror(filter('Sheet1'!$B:$B;'Sheet1'!$B:$B=$A3;text('Sheet1'!$C:$C;"MMM-yyyy")=B$1)));counta(iferror(filter('Sheet1'!$B:$B;'Sheet1'!$B:$B=$A3;text('Sheet1'!$D:$D;"MMM-yyyy")=B$1)))))


Copy (or drag) the above formula to Cell B4, then I have the following formula in Cell B4 of Sheet 2:

=ArrayFormula(if({1,0};counta(iferror(filter('Sheet1'!$B:$B;'Sheet1'!$B:$B=$A4;text('Sheet1'!$C:$C;"MMM-yyyy")=B$1)));counta(iferror(filter('Sheet1'!$B:$B;'Sheet1'!$B:$B=$A4;text('Sheet1'!$D:$D;"MMM-yyyy")=B$1)))))


Similarly now copy the formula from B3 to D3 (Note: Leave Cell C3 as it is going to auto populate)
So, I have the following formula in Cell D3 of Sheet 2:

=ArrayFormula(if({1,0};counta(iferror(filter('Sheet1'!$B:$B;'Sheet1'!$B:$B=$A3;text('Sheet1'!$C:$C;"MMM-yyyy")=D$1)));counta(iferror(filter('Sheet1'!$B:$B;'Sheet1'!$B:$B=$A3;text('Sheet1'!$D:$D;"MMM-yyyy")=D$1)))))


And if you want the solution with a single formula (but it would not be as the exact desired output), then have a look at the following link:

http://igoogledrive.blogspot.com/2013/07/formatting-data-in-pivot-table.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:

http://igoogledrive.blogspot.com/p/paid-projects.html

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 

Thanks,

Kishan.

iGoogleDrive