# Sum specific cells in column based on multiple row criteria

#### generalmonk

##### New Member
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.

### Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

#### sandy666

##### Banned - Rules violations
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

##### Well-known Member
Try Using this (for row 1 to 44):

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

you can change "AB5" with range of criteria.

#### generalmonk

##### New Member
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

##### New Member

Try Using this (for row 1 to 44):

Excel Formula:
``=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

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

#### sandy666

##### Banned - Rules violations

 Sum 300

Power Query:
``````// 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

##### New Member
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

##### New Member
 Sum 300

Power Query:
``````// 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.

Replies
3
Views
152
Replies
1
Views
108
Replies
2
Views
380
Replies
1
Views
29
Replies
10
Views
154

1,127,245
Messages
5,623,586
Members
415,981
Latest member
Baltwin

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back