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

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

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

31603

which you'd want summarising something like:

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?

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

31603

which you'd want summarising something like:

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

[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