## Sunday, July 14, 2013

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

Thanks,