Friday, August 24, 2012

yogi_Create A Table Of Count Of Sales By Product And Month From Data In Another Sheet

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #713   Aug 24, 2012

user squash said: (!category-topic/docs/spreadsheets/ZbFTQNCipwA)
Automatically adjusting a formula that uses a date
My spreadsheet is for tracking sales.
On Sheet 1, I'm tallying sales
Column A is the Date I'm open for business
The rest of the columns are the different items I'm selling, tallying each of the items
On Sheet 2, I'm calculating monthly totals
Column A has the various items, and the months are in Columns B-M 
I'm using this formula 
=SUM(filter('Sheet 1'!C2:C,'Sheet 1'!A2:A>=date(2012,1,1),'Sheet 1'!A2:A<=date(2012,1,31)))
to automatically calculate the number of units (tallied in Column C) I've sold of a particular item (in January 2012, in this instance).
The next item's numbers are in Column D, etc etc
My question is, is there an easy way to adjust this formula for the different months and different items? I don't know an easy way to "find and replace" pieces of a formula. Dragging the formula down doesn't make the correct change to it for the different items, and more importantly, I can't figure out any way to change the dates for the different months besides going in and doing it manually. I thought I might be able to put the portion of the formula that deals with the date
'Sheet 1'!A2:A>=date(2012,1,1),'Sheet 1'!A2:A<=date(2012,1,31)
into its own cell, and then jut reference that cell for all the formulas for a given month, but it returns errors.
Anyone have any clever ideas here? Your help is much appreciated. Thank you in advance.

following is a solution to the problem