Tuesday, July 16, 2013

yogi_Develop Stats By SalesRep From A SalesTable Using Multiple Criteria

                                          Google Spreadsheet   Post  #1287
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jul 15, 2013
user Stupac310 (http://productforums.google.com/forum/?zx=yx0vn6gx56ds#!mydiscussions/docs/4bICingQ4IQ)
Count using multiple criteria including date range
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.

Hey Yogi, thanks for picking up the thread... per your instructions...

Sample Spreadsheet: https://docs.google.com/a/chownow.com/spreadsheet/ccc?key=0Agzd3UPZ0QaOdGMwNExsMkdJN09zelcya1NxUVA5QlE#gid=1

(a) i need help with the formulas to calculate the number of sales within a date range (monthly) and, separately, the number of subsequent cancellations form that month's sales; please note that it is not the number of cancellations that occurred in a given month, but rather the number of sales made in a certain month that were subsequently cancelled. (for example, 5 sales in January 2013, with 3 that were cancelled later that year, so the numbers appearing under Jan-2013 would be Sales = 5 and Cancellations =3)
(b) the formulas would be entered in the worksheet entitled Calculations; specifically in the Sales and Cans column cells under each month (i.e., columns E and F, columns H and I, etc.).
(c) i hardwired the expected results.

Further explanation... i believe that a filter screening for sales rep and date range (columns E and C in the Master worksheet) would yield the sales in each month; and that a filter screening for sales rep, date range, and Cancelled in the Status column (columns E, C and B in the Master worksheet) would yield the total subsequent cancellations of a given month's sales. However this approach crashed my document.

I'm grateful for any light you can shed on an efficient way to do this. Thanks, Yogi!