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.
(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!
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.
(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!
-------------------------------------------------------------------------------------------------------------------------------------------------------
No comments:
Post a Comment