customer Turnover Calculations

[jamesnimmo]

Help,

I am trying to create a spreadsheet based off a large query spreadsheet. Image attached shows a sample of data.

Bill-to Customer No_	No_	Name	Salesperson Code	Shortcut Dimension 1 Code	Posting Date	Sum of Unit Cost (LCY)	Sales T/O
001772	136870	Bobs bits	JW	IN	01/03/2013	461.53202	770
001772	136871	Bobs bits	JW	IN	19/03/2013	290.11049	607
001772	136872	Bobs bits	JW	IN	22/03/2013	284.34489	684
001773	136873	Michels Bits	JW	IN	01/02/2013	43.61652	121
001773	136874	Michels Bits	JW	IN	20/02/2013	156.23657	320.5
001773	136875	Michels Bits	JW	IN	20/03/2013	212.50332	641
002192	136876	Work Bits	JW	IN	01/01/2013	188.1683	466.5
002192	136877	Work Bits	JW	IN	05/02/2013	213.06259	385
002192	136878	Work Bits	JW	IN	20/03/2013	208.26	413
Customer No	Jan T/O	Feb T/O	March T/O
001772
001773
002192

<colgroup><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

I think a sum product code is what i need to be using, but i cannot figure out how to get it to look for matching criteria (i.e. all the Same Bill-to Customer No) and add all the data together (sales T/O) during a date range (01/01/13 - 31/01/13, 01/02/13 - 2802/13, 01/03/13 - 31/03/13,)

Any help would be great..

 

[Andrew Poulsom]

Can't you use a pivot table, grouping the dates by month and year?

 

[jamesnimmo]

Can't you use a pivot table, grouping the dates by month and year?

I would like to be able to keep a historical log of it, rather than just using a pivot, and set it up so that it updates on each refresh.

 

[Andrew Poulsom]

You can use a formula like this in B14 copied down and across:

=SUMPRODUCT(--($A$2:$A$10=$A14),--(TEXT($F$2:$F$10,"mmm")=LEFT(B$13,3)),$H$2:$H$10)