Sum specific cells in column based on multiple row criteria

[generalmonk]

I cannot find an answer to this anywhere, so hoping you can help! I have a list of ref IDs in Column A which may or may not be unique (eg. 1, 1, 1, 2, 3, 3, 4, 4 ,4 ,4...etc). I need to be able to find the last instance of EACH of these ref IDs in Column A and sum the associated cell values in a column further along in the Spreadsheet. Many thanks in advance.

 

[sandy666]

something like this?

ID	Number		ID	Last
1	36		1	96
1	98		2	58
1	72		3	52
1	96		4	24
2	58		5	34
3	68		Total	264
3	30
3	43
3	52
4	67
4	95
4	24
5	34

 

[maabadi]

Try Using this (for row 1 to 44):

=LOOKUP(2,1/($A$2:A44="AB5"),B2:B44)

you can change "AB5" with range of criteria.

 

[generalmonk]

something like this?

ID	Number		ID	Last
1	36		1	96
1	98		2	58
1	72		3	52
1	96		4	24
2	58		5	34
3	68		Total	264
3	30
3	43
3	52
4	67
4	95
4	24
5	34

something like this?

ID	Number		ID	Last
1	36		1	96
1	98		2	58
1	72		3	52
1	96		4	24
2	58		5	34
3	68		Total	264
3	30
3	43
3	52
4	67
4	95
4	24
5	34

Yes, exactly, but i cant have the extra helper table, i just need the sum. Cheers.

 

[generalmonk]

Try Using this (for row 1 to 44):

=LOOKUP(2,1/($A$2:A44="AB5"),B2:B44)

you can change "AB5" with range of criteria.

Thanks v much for the response, I'll try this and report back…

 

[generalmonk]

… actually the range of criteria is in Column A itself and is not set/known.Will this be an issue with your formula?

 

[sandy666]

Sum
300

// Sum
let
    Source = Excel.CurrentWorkbook(){[Name="Table5"]}[Content],
    Group = Table.Group(Source, {"ID"}, {{"Count", each _, type table}}),
    Last = Table.AddColumn(Group, "Last", each List.Last([Count][Number])),
    Sum = List.Sum(Last[Last])
in
    Sum

 

[generalmonk]

Thanks v much for the response, I'll try this and report back…

Hi Maabadi, please could you explain what you mean by "Change AB5 with range of criteria"? What criteria should I be using? The criteria is the last instance of each Ref ID in Column A... how do I write this in the formula? Many thanks in advance...

 

[generalmonk]

Sum
300

// Sum
let
    Source = Excel.CurrentWorkbook(){[Name="Table5"]}[Content],
    Group = Table.Group(Source, {"ID"}, {{"Count", each _, type table}}),
    Last = Table.AddColumn(Group, "Last", each List.Last([Count][Number])),
    Sum = List.Sum(Last[Last])
in
    Sum

Sorry Sandy666, I have no idea how to use this code. Is my request possible via formula instead? Cheers.

 

[sandy666]

s666-PQ-sum.xlsx

Microsoft Excel Workbook

1drv.ms

Power Query documentation - Power Query

Power Query is used to filter, combine, and mash up data from one or more supported data sources.

docs.microsoft.com