2

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.

1
  • Suggested an edit removing the request to email the answer and tidy up the heading. Jul 5, 2012 at 16:30

2 Answers 2

2

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)
1

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)

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .