Min with sumproduct

Hi, I need a formula that finds the minimum from one col if criteria is met in another col, example:

A1:A4: 3,2,4,1
B1:B4: B,C,A,D

If the criteria I need is "D" and the minimum for the whole set of A1:A4 (which is 1 above) corresponds, then true.

I don't need the min only for the group D, example:

A1:A4: 1,2,4,5
B1:B4: B,D,A,D

Here the min for "D" is 2 but it is not the min for the whole set, I don't want that. I want only if "D" is matched with "1".
Thanks.

How about...

=MIN(IF(B1:B4="D",A1:A4))

IMPORTANT
This is an array formula
Enter the formula >> press F2 then >> CTRL + SHIFT + ENTER
If entered correctly, the formula will be enclosed in {brackets}
Do not enter the {brackets} manually

I want only if "D" is matched with "1".

Maybe some more explaining is necessary then. I don't follow

Why not explain it with a few more examples thru a workbook?

Either that or maybe somebody else can give it a go...

Originally Posted by JJGF

No, this returns the wrong result.

What did it return, and what were you expecting?

Did you use CTRL SHIFT ENTER to enter this, and not just enter?

Maybe:

Formula:

Please Login or Register  to view this content.

Regards, TMS

JJGF, did you see post # 7?

OK, well you have been asked a few different questions, a few different times, without providing any answers to them. We are here to help, but only if you help us to help you.

Like Jeff, I will now leave this to someone else, maube they can read your mind better than we can - good luck

Glad you have found a solution to your problem.

Please bear in mind that, if you provide sample data that does not represent the problem you wish to address, you will get solutions offered that do not meet your needs. And, in the process, you are likely to alienate the people you are looking to for an answer. Even when you did provide a sample workbook, the data was not truly representative of the scenario you need to address.

Regards, TMS

Nope, not having any trouble at all

That is your opinion, ...

Yep.

... the sample workbook explained everything perfectly.

Nope. The data and explanation seems, as far as I can see, to reflect your original post. However, it does not cater for duplicate values as described in your post #9.

But, worry not about my opinion, and I will worry not about your problems

JJFG, a word of advice to CLOSE off this thread...

When members ask for additional info, it is often best to try and answer IN the post, rather than making them open up a file unnecessarily.

Stating "it didnt work" is hardly descriptive of what happened, as compared to "It didnt work as expected, it should have returned xx, but instead returned yy".
Most experienced members here have enough knowledge that they can probably figure out what went wrong just by what you said you got vs what you wanted.

Im sure it was not your intention, but it appears that you have ruffled more than a few feathers here, which is not very condusive to getting any future help you might need

Again, I am happy you got your question resolved, enjoy your stay with us

I believe this thread is now concluded?

We all have "off" days when we think what we said was pretty obvious (but was not lol) - and I mean from both sides here. Thanks for the understanding

No harm done, we look forward to assisting you in the future

Originally Posted by JJGF

=IF(MIN(A1:A4)=AGGREGATE(15,6,A1:A4/(B1:B4="D"),1),1,"")

Perhaps also try COUNTIFS like this

=IF(COUNTIFS(A1:A4,MIN(A1:A4),B1:B4,"D"),1,"")

That will give you the same results except if you have no "D" in B1:B4 - in which case my suggestion returns a blank rather than an error