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.
 

Some videos you may like

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
Joined
Oct 24, 2015
Messages
7,499
something like this?
IDNumberIDLast
136196
198258
172352
196424
258534
368Total264
330
343
352
467
495
424
534
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,603
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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
Joined
Dec 6, 2020
Messages
8
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Dec 6, 2020
Messages
8
Office Version
  1. 365
Platform
  1. Windows
… 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
Joined
Oct 24, 2015
Messages
7,499

ADVERTISEMENT

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

Watch MrExcel Video

Forum statistics

Threads
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.
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
Top