Formula subtotal if

[ChristianDK]

Hi

I am new here in this forum, so if I'm not doing it right please do tell.

The best solution to my problem is if Microsoft adds/have a subtotal-if function to excel.

What I am trying to do it combine the two as I only want to look at specific rows, and at the same time return the minimum value of all the specific rows.


I have made a simple example of my problem:

Car#	Price	Best price
1	100	100
2	150	125
3	200	150
3	150	150
2	125	125
1	125	100

<tbody>
</tbody>

The formula I am looking for is for row "Best price" where I need excel to look at all the rows with the same car# and return the lowest price, so the subtotal formula then returns the minimum value, but only take into account rows if the car# is a match.

I have filled out "Best price" manually to show want I am trying to do.

In my real data, I got more than 24.000 rows that I need to sort out and my boss is on my back big time, so I really hope a kind soul will help me out.


Thank you in advance.

Br. Christian

 

[Peter_SSs]

Welcome to the MrExcel board!

From what you are describing, I don't think the subtotal function is what you need (perhaps I have not understood what you want?)

If you are using Excel in Office 365, try the C2 formula copied down, otherwise for for all versions from Excel 2010 try the D2 formula

Excel Workbook
	A	B	C	D
1	Car#	Price	Best price	Best price
2	1	100	100	100
3	2	150	125	125
4	3	200	150	150
5	3	150	150	150
6	2	125	125	125
7	1	125	100	100
Sheet4

 

[ChristianDK]

Thank you so much!

I used the AGGREGATE formula, and it worked. Abit slow on 24,000 ( crashed the server for 10 min ) rows but it does give the correct values.

Are you a wizard? The formula doesn't make any sense regarding the divide part, as in my real data i don't have numbers but car license plates, so it must be magic.

Spreadsheet Formulas

Cell Formula C2 =MINIFS(B$2:B$7,A$2:A$7,A2) D2 =AGGREGATE(15,6,C$2:C$7/(A$2:A$7=A2),1) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4 [/QUOTE]

 

[Peter_SSs]

1. I had an error in my D2 formula, hope you noticed that. It should have been
=AGGREGATE(15,6,B$2:B$7/(A$2:A$7=A2),1)
though that won't resolve the speed issue.

2. Pity you couldn't use the MINIFS - it is a good amount quicker.

3. This one should be a little faster than the AGGREGATE, but not a lot. It is an array formula so should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}. The formula can then be copied down.
{=MIN(IF(A$2:A$7=A2,B$2:B$7))}

 

[ChristianDK]

Once again thank you so much!

I did notes the B vs C error, but a minor mistake compared to the great help.

I cannot get new formula {=MIN(IF(A$2:A$7=A2,B$2:B$7))} to work, so I will just have to take an extra kop of coffee when I calculate the sheet.

 

[Peter_SSs]

I cannot get new formula {=MIN(IF(A$2:A$7=A2,B$2:B$7))} to work, so I will just have to take an extra kop of coffee when I calculate the sheet.

Did you carefully follow this?

.. should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter.

Of course you would again have to adjust the ranges to suit your data. And any time you edit the formula you have to again confirm it with Ctrl+Shift+Enter

 

[ChristianDK]

Did you carefully follow this?

Of course you would again have to adjust the ranges to suit your data. And any time you edit the formula you have to again confirm it with Ctrl+Shift+Enter

Yes, and excel did at the two {} after I pressed Ctrl+Shift+Enter, but the formula returns an error message, "Name"

 

[Peter_SSs]

Yes, and excel did at the two {} after I pressed Ctrl+Shift+Enter, but the formula returns an error message, "Name"

Check your spelling of the function names, including any accidental space characters in them.