Sumproduct

[warrima]

Hi all,

I am updating the attached report calculator. On the calculator tab i am adding a new table (right hand side) that will sum up the value of orders recieved from each customer for every month. I need the formula to check the date the order was recieved, check from which customer and then some the value of the orders from collumn z (all data in the new orders tab)

Ive completely forgotten how to do this and would appreciate the help

Thanks all

[DonkeyOte]

First, change your headers to be Date values, eg P5 = 1/1/2009 (format to mmm if you want only to see Jan)

Second, revise formula as follows:

Please Login or Register  to view this content.

You would however be best served using a Pivot Table.

[warrima]

Thanks DonkeyOte,

As you suggest i do just want the total sales figure per customer per month. I entered the formula you suggested and it works for the actual date dd/mm/yy but not if i use mmm to get the sales figure for whole month. Have i entered incorrectly?

I tried to use the pivot table like your second suggestion - have you an example of how i could use this. It keeps throwing up errors saying the data is in merged cells. Again i havent used Pivot tables much before but if i can see an example using my sales sheet i could pick it up.

TQ in advance.

[DonkeyOte]

I entered the formula you suggested and it works for the actual date dd/mm/yy but not if i use mmm to get the sales figure for whole month. Have i entered incorrectly?

I think you're missing the underlying premise. Your headers on your summary table should be dates not text string, so for Jan you should enter 1/1/2009 not Jan... if you want the header to display "Jan" then my point was - use a Custom Format on the date itself.

Why use 1st of month ?

The formula provided does not sum just 1/1/2009 rather it sums all of Jan 2009 by normalising the 'New Orders' dates. How ? Well for ex. let's say

A1: 10th Jan
A2: 23rd Jan
A3: 3rd Feb

The formula

=A1-DAY(A1)+1
copied down

will "normalise" the dates such that they all become 1st of month

A1: =10th Jan - 10 + 1 -> 1st Jan
A2: =23rd Jan - 23 + 1 -> 1st Jan
A3: =3rd Feb - 3 + 1 -> 1st Feb

so the SUMPRODUCT provided normalises the dates on source sheet to be 1st of month and compares those dates to the header which is also 1st of month.

[warrima]

Ahhhhhhhhhhh I see. Thanks for that, very useful.

Cheers