If Cell = Name, Calculate Max/Min From Rows Where Cell = Name

[KEARNJ]

Hi all,

First time posting so forgive me if there is a fairly straightforward formula that I am unaware of.

I am looking to derive a maximum and minimum price for roughly 500 investments over a year where I have a daily price for almost each business day (~120,000 cells). I think I have articulated the rough formula that I am looking to create in the title of the thread but have been struggling to recreate this with MAXIFS & MINIFS formulas.

Effectively, if the investment name (column B) = X, I want to calculate the maximum and minimum prices (in a range in column E), in all rows where column B = X.

Any advice appreciated! I figured this would likely be quicker than going scrolling 120,000 cells and ensuring I have the right range for each investment...

[Richard Buttrey]

Hi, and welcome to the forum.

Presumably something like

Please Login or Register  to view this content.

[Sam Capricci]

either should work something like these...
=MAXIFS($E$2:$E$18,$B$2:$B$18,B2)
and
=MINIFS($E$2:$E$18,$B$2:$B$18,B2)
but with extended ranges.
and if you have a distinct list of the names point to that instead.

[KEARNJ]

Fantastic both! Thank you so much.