# Financial Report - Pivot table doesn't do it.

#### monkeyharris

##### Active Member
I have a sheet where all my finanal data is stored and want to create report form this which gives mt the following headers and then sort by Month, then highest turner over by customer. Example:

Month (from column DR) - Customer (from column B) - Turnover (Column DX) - profit (Column DY).

Result based on the below for LFS is: JAN - LFS - £7482.06 - £473.47

I could do a sumproduct but that only works if i know the the customers but the problem is i won't and don;t want to have to create a new row each time a new customer comes on board.

This is my core data: SD0001 is cell A3 and the last column "Profit" is DY3.

### Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

#### DonkeyOte

##### MrExcel MVP
Can you elaborate as to why a Pivot isn't fit for purpose ?

To me at least a Pivot Table would seem ideal - esp. given the ordering etc you intend to apply to your formulae.

I added a few more dummy transactions to your data so as to generate a two month view and from that I generated the below:

Excel Workbook
ABCDE
3Data
4YearsINV DATECUSTOMERSum of TOTAL OUTSum of PROFIT
52010JanLFS7482.09473.49
6Capespan PTY5222.26296.76
7Harwood Shipping365245
9Jan Total13309.351080.25
10FebJXZY550.8729.71
11LFS500.4450.44
12Harwood Shipping365245
13Feb Total1416.31325.15
14Grand Total14725.661405.4
Sheet3

The above PT is sourced from a Dynamic Named Range called _PTData.

Given we don't know the name of this sheet the simplest way to create this Name is to go to the sheet containing the data and insert below in RefersTo dialog:

=\$A\$2:INDEX(\$DY:\$DY,MATCH(REPT("Z",255),\$A:\$A))

The Year/Month breakdown is sourced from Columns INV DATE and is grouped by Year & Month.

Field CUSTOMER is sorted based on Sum of TOTAL OUT Desc.

Subtotals / Grand Totals can obviously be removed as required.

Replies
5
Views
999
Replies
0
Views
415
Replies
13
Views
1K
Replies
3
Views
345
Replies
12
Views
452

1,132,685
Messages
5,654,745
Members
418,149
Latest member
amamiche67

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

### Which adblocker are you using?

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

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