Excel function: MIN per category

Question

I have a question regarding how to calculate minimum cost (using MIN function) per category of items.

+-------------------------------------------+-------+------+
|                  A                        |   B   |   C  |
+-------------------------------------------+-------+------+
| TD Canadian Money Market Fund Series A    |  0.56 |      |
+-------------------------------------------+-------+------+
| TD Canadian Money Market Fund Series F    |  0.43 |      |
+-------------------------------------------+-------+------+
| Invesco Allocation Fund Series A          |  0.02 |      |
+-------------------------------------------+-------+------+
| Invesco Allocation Fund Series F          |  0.32 |      |
+-------------------------------------------+-------+------+

I'm trying to figure out how to return in Column C the lowest cost (Column B) per each fund (Column A) so that I could filter out all the more expensive funds and only have the data for the least expensive Class of each fund. I would like it to remain using excel functions only and not VB code if possible.

Thanks a lot in advance for the help, it will be very appreciated.

Gabriel

So there are multiple rows for each of the four funds mentioned in A? Or do you want them treated as one thing if they are the same fund and just have a different series letter? — Jerry Jeremiah
Why not use a pivottable? It would be much easier and the functionality is built right in for this purpose. You can add min, max, average, count, etc all with a few clicks. — Brandon Barney

godspeed23.carl godspeed23.carl · Accepted Answer · 2017-02-28T00:45:33

If you want it to be blank for the non-min values and show only for the actual min, you could using something like:

=IF(B1=MIN($B:$B),B1,"")

for C1 and copy down the formula

Excel function: MIN per category

1 Answers