I am very conversant with pivot analysis but I do not want to use it for this for my table.
I want to be able to make calculations between a date range. For instance, sales of a particular product from 1st of April to 22nd of June.
2 Answers
You can use SUMPRODUCT() for this, the ff. formula in particular:
=SUMPRODUCT(--(dates>=start_date),--(dates<=end_date),--(products=product_name),prices)
If you don't want to take the product name into consideration, just remove its corresponding parameter, like so:
=SUMPRODUCT(--(dates>=start_date),--(dates<=end_date),prices)
Where:
dates refers to the range that contains your dates (Column A in my example below)
products refers to the range that contains product names (Column B in my example below)
prices refers to the range that you'd like to sum up (Column C in my example below)
Example:
Suppose I have the ff. table:
To get the sum of the prices for Product C starting from 6/20/2012 to 7/3/2012, use the following formula:
=SUMPRODUCT(--(dates>=DATE(2012,6,20)),--(dates<=DATE(2012,7,3)),
--(products="Product C"),prices)
You could also use helper cells to contain the "criteria" for your sum, like the ff:
The yellow cell above contains the formula:
=SUMPRODUCT(--(dates>=F2),--(dates<=G2),--(products=E2),prices)
It would defeat the purpose of an open question and answer forum to email an answer to you...
Two choices:
You need to set up an area of your sheet to construct your query parameters and then use DSUM to sum your data based on those. see:
OR use SUMIFS function with a series of conditions which must all be logically AND (which should suit you to do product="X", date>1st April, date<22nd June etc)
