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

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)

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy