Sunday, July 14, 2013

Google Spreadsheet Filtering and Formatting data


( 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.


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:


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


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:


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: 


No comments:

Post a Comment