Sum (column) value across multiple column criteri

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.

Code:

---

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

---

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 :
Attachment 31595
I'll await your response before getting into detail.

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.

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

Attachment 31598

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

Hi Paul, thank for the reply

I'll give it a nudge in the morning.

Quote:

---

Originally Posted by demetre

" 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:

Attachment 31603

which you'd want summarising something like:

Attachment 31604

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?

Quote:

---

Originally Posted by demetre

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

Quote:

---

Originally Posted by p45cal

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:

Attachment 31603

which you'd want summarising something like:

Attachment 31604

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

Quote:

---

Originally Posted by Paul_Hossler

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

Quote:

---

Originally Posted by Paul_Hossler

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