Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question
    
Suggested an edit removing the request to email the answer and tidy up the heading. –  Julian Knight Jul 5 '12 at 16:30

2 Answers 2

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: http://office.microsoft.com/en-us/excel-help/dsum-function-HP010342460.aspx

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)

share|improve this answer

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:

enter image description here

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:

enter image description here

The yellow cell above contains the formula:

=SUMPRODUCT(--(dates>=F2),--(dates<=G2),--(products=E2),prices)
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.