Extracting monthly totals per customer

[FrecklePaw]

Hello,

I have some financial info from various different customers, which has been input into a spreadsheet with each individual purchased item input. However, I need to extract only the monthly totals for all items purchased for each customer (i.e. not itemised but a total for all).

BUT there is over 14 months' worth of data (and several customers!) so I am looking to find a way to shorten this exercise quickly without having to manually do too much or, worse, have to manually input each month's total myself. Is there a function or method to quickly extract this data and alter the existing sheet without also having to do too much backwards and forwards (i.e. cut and paste into another new sheet, etc.)?

TIA,
FP

[HansV]

Shouldn't this be in the Excel forum?

Could you attach a small sample workbook (without sensitive information)?

[FrecklePaw]

Ah! Sorry yes - I had thought I had clicked on the Excel one! I will move it over to there now.
I can send a screenshot meantime - will organise to send...

[HansV]

I have moved this thread from the Word forum to the Excel forum.

[hamster]

You were asked about Excel example not a screenshot

anyway you can try Power Query : Group by month and Sum to MonthTotal

[robertocm]

Here an example using ADO:

Libro1.xlsm

[hamster]

Power Query
(however you can use Pivot )

Code: Select all

// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Month = Table.AddColumn(Source, "Month", each Date.Month([Date]), Int64.Type),
    Grp = Table.Group(Month, {"Person", "Month"}, {{"MonthTot", each List.Sum([Sales]), type number}}),
    Asc = Table.Sort(Grp,{{"Month", Order.Ascending}})
in
    Asc

pqtot.png

excel file contain extended version