Sum specific cells in column based on multiple row criteria

generalmonk

New Member
Joined
Dec 6, 2020
Messages
8
Office Version
  1. 365
Platform
  1. Windows
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

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
something like this?
IDNumberIDLast
136196
198258
172352
196424
258534
368Total264
330
343
352
467
495
424
534
 
Upvote 0
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.
 
Upvote 0
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…
 
Upvote 0
… actually the range of criteria is in Column A itself and is not set/known.Will this be an issue with your formula?
 
Upvote 0
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
 
Upvote 0
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...
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top