# customer Turnover Calculations

#### jamesnimmo

##### New Member
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..

### Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Can't you use a pivot table, grouping the dates by month and year?

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.

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)

Replies
5
Views
270
Replies
1
Views
83
Replies
6
Views
198
Replies
0
Views
218
Replies
7
Views
418

1,196,048
Messages
6,013,096
Members
441,747
Latest member
darkman77

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back