Sum (column) value across multiple column criteri

[demetre]

Hi All, I’m having difficulty working out how to sum (column) across multiple columns (criteria) and then delete those records except for one record (contains the sum).

SourceFile

Classification	FullName	Type	Date	AmountLocal	Country	AmountRegion	Region
ABG	AlphaBetaGamma	White	18/01/2024	5.00	GER	6.50	AUS
ABG	AlphaBetaGamma	White	18/01/2024	7.00	GER	9.00	AUS
ABG	AlphaBetaGamma	Blue	18/01/2024	5.00	GER	6.50	AUS
ABC	AlphaBetaCorr	Green	19/01/2024	3.50	GER	5.00	AUS

PseudoCode
Where multiple (same date) records exists (criteria: FullName, Date, Type) sum (criteria: AmountLocal), keep one record (where used for sum) and delete other records (used in sum) but leave existing (other non impacted) records

As per above
2x dupe dates per FullName per Type, sum {5.00, 7.00}
And output I'm aiming for is

Classification	FullName	Type	Date	AmountLocal	Country	AmountRegion	Region
ABG	AlphaBetaGamma	White	18/01/2024	12.00	GER	15.50	AUS
ABG	AlphaBetaGamma	Blue	18/01/2024	5.00	GER	6.50	AUS
ABC	AlphaBetaCorr	Green	19/01/2024	3.50	GER	5.00	AUS

I've tried SUMIFS, SUMPRODUCT but cannot get it to work.

Any and all help appreciated; thank you.

Sub sumData()
     Dim ws As Worksheet
     Dim As lLastRow As Long
     Dim sumRange As Range, partyRange as Range, typeRange As Range, dateRange As Range

     Set ws = ThisWorkbook.Sheets("SourceFiles")

     With ws
          lLastRow = .Cells(.Row.Count, "B").End(xlUp).Row 'Define last row
          
          set partyRange = ws.Range("B2:B" & lLastRow)'Party name
          set typeRange = ws.Range("C2:C" & lLastRow)'Type name
          set dateRange = ws.Range("D2:D" & lLastRow)'Date range
          set sumRange = ws.Range("E2:E" & lLastRow)'Costs range
          
          'Find records which Name + same Date + same Type > 1 record
          'Sum the Costs into "E2:E"
          

          'I tried and other methods
         .Range("M2:M" & lLastRow) = WorksheetFunction.SumIfs(partyRange, typeRange, dateRange, sumRange)
          
          'Delete other duplicate rows

End Sub

[p45cal]

It doesn't apply to your sample data, but if you group by FullName, Date and Type, what happens when there are different: Classifications, Countries, Regions within that group? What do you want to see in those columns? If you can tell me they'll always be the same then it's easy to do either a pivot table or Power Query by grouping by everything except AmountLocal and AmountRegion :
2024-05-27_214237.jpg
I'll await your response before getting into detail.

[demetre]

Hi p45cal, thanks for the reply

" what happens when there are different: Classifications, Countries, Regions within that group?" --> Classification, countries and regions will be agnostic to the outcome. So they'll stay as they are; static.

My main challenge is how to sum amountLocal by FullName, Type & Date; then deleting the duplicate rows to leave just 1 row post summation.

I've not used Power Query previously.

[Paul_Hossler]

A 'no VBA required' way would be use a pivot table for the heavy lifting and analysis

Capture.JPG

If you wanted, you could use VBA to copy/paste the PT and format it the way you want fpr presentation

Personally, I try to keep (1) the data seperate from (2) the analysis and seperate from the (3) presentation, instead of trying to do everything all at once

[demetre]

Hi Paul, thank for the reply

I'll give it a nudge in the morning.

[p45cal]

" what happens when there are different: Classifications, Countries, Regions within that group?" --> Classification, countries and regions will be agnostic to the outcome. So they'll stay as they are; static.
My main challenge is how to sum amountLocal by FullName, Type & Date; then deleting the duplicate rows to leave just 1 row post summation.

I think I've understood the problem; I'm just asking what you want in slightly different circumstances (and if such circumstances ever happen). For example here are 4 rows all with the same FullName, Type and Date:

2024-05-28_121027.jpg

which you'd want summarising something like:

2024-05-28_121143.jpg

but look carefully at the classification column of the source data, 2 rows are ABC and two rows ABG. So what do you want to appear in the classification column of the summary? It's not a case of them being static or agnostic, it's what do you want to see?

[Paul_Hossler]

Hi Paul, thank for the reply

I'll give it a nudge in the morning.

As an EXAMPLE, the attached WB has a data sheet, a hidden PT sheet, and a display results sheet

The macro refreshes the PT and copies and formats the result onto the display sheet

Changes to the PT are automatically picked up (formats, subtotals, etc.) so if you wanted to change order, etc. it's IMO easier

[demetre]

I think I've understood the problem; I'm just asking what you want in slightly different circumstances (and if such circumstances ever happen). For example here are 4 rows all with the same FullName, Type and Date:

2024-05-28_121027.jpg

which you'd want summarising something like:

2024-05-28_121143.jpg

but look carefully at the classification column of the source data, 2 rows are ABC and two rows ABG. So what do you want to appear in the classification column of the summary? It's not a case of them being static or agnostic, it's what do you want to see?

I missed that p45Cal; so that'll also need to be a range to filter on. Great pickup. The classification needs to be considered in the summation given there could be a difference between them.
So it'll be a sum (AmountLocal) based on range criteria {classification, FullName, Type, Date}
Cheers

[demetre]

As an EXAMPLE, the attached WB has a data sheet, a hidden PT sheet, and a display results sheet

The macro refreshes the PT and copies and formats the result onto the display sheet

Changes to the PT are automatically picked up (formats, subtotals, etc.) so if you wanted to change order, etc. it's IMO easier

Hi Paul
having a look at it now
Cheers

[demetre]

As an EXAMPLE, the attached WB has a data sheet, a hidden PT sheet, and a display results sheet

The macro refreshes the PT and copies and formats the result onto the display sheet

Changes to the PT are automatically picked up (formats, subtotals, etc.) so if you wanted to change order, etc. it's IMO easier

Thanks very much Paul for the assistance