array formula using 2 criteria

[nisselyo]

My formula works when I use only one criteria but when I include an and with a second criteria it doesnt work - any idea why?

This one works:
=MIN(IF('Citi - Sheet1'!$C$2:$C$10091=B107,'Citi - Sheet1'!$E$2:$E$10091))
and this one works
=MIN(IF('Citi - Sheet1'!$D$2:$D$10091=45,'Citi - Sheet1'!$E$2:$E$10091))

but when I combine these 2 criteria I get 0 - which is incorrect
=MIN(IF(AND('Citi - Sheet1'!$C$2:$C$10091=B107,'Citi - Sheet1'!$D$2:$D$10091=45),'Citi - Sheet1'!$E$2:$E$10091))

Thanks,
Yosef

 

[Aladin Akyurek]

My formula works when I use only one criteria but when I include an and with a second criteria it doesnt work - any idea why?

This one works:
=MIN(IF('Citi - Sheet1'!$C$2:$C$10091=B107,'Citi - Sheet1'!$E$2:$E$10091))
and this one works
=MIN(IF('Citi - Sheet1'!$D$2:$D$10091=45,'Citi - Sheet1'!$E$2:$E$10091))

but when I combine these 2 criteria I get 0 - which is incorrect
=MIN(IF(AND('Citi - Sheet1'!$C$2:$C$10091=B107,'Citi - Sheet1'!$D$2:$D$10091=45),'Citi - Sheet1'!$E$2:$E$10091))

Thanks,
Yosef

Control+shift+enter...

=MIN(IF('Citi - Sheet1'!$C$2:$C$10091=B107,IF('Citi - Sheet1'!$D$2:$D$10091=45,'Citi - Sheet1'!$E$2:$E$10091)))

 

[nisselyo]

thanks.

 

[nisselyo]

It worked like a charm - thanks. One follow up question
Im looking up the lowest rate with tose variables. my next issue is that some of the rates have a cost (which is the net column over) of #n/a. Is there a way to find the minimum with these criteria - but fotr where the cost is a number and not this error?
Thanks,
Yosef

 

[Aladin Akyurek]

It worked like a charm - thanks. One follow up question
Im looking up the lowest rate with tose variables. my next issue is that some of the rates have a cost (which is the net column over) of #n/a. Is there a way to find the minimum with these criteria - but fotr where the cost is a number and not this error?
Thanks,
Yosef

Assuming that 'Citi - Sheet1'!$E$2:$E$10091 might contain #N/A...

Control+shift+enter:

=MIN(IF('Citi - Sheet1'!$C$2:$C$10091=B107,IF('Citi - Sheet1'!$D$2:$D$10091=45,IF(ISNUMBER('Citi - Sheet1'!$E$2:$E$10091),'Citi - Sheet1'!$E$2:$E$10091))))