SOLVED

MINIFS function

%3CLINGO-SUB%20id%3D%22lingo-sub-2632789%22%20slang%3D%22en-US%22%3EMINIFS%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2632789%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3EI%20have%20data%20in%20the%20below%20form%20%3A%3C%2FP%3E%3CTABLE%20width%3D%22182%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2264%22%3EID%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22118%22%3ETime%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E08%2F04%2F21%2023%3A11%3A00%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E08%2F04%2F21%2022%3A49%3A00%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E%23VALUE!%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhen%20I%20use%20the%20Minif%20function%20to%20get%20the%20min%20time%20for%20this%20id%2C%20I%20am%20getting%20the%20answer%20as%20%23Value!.%20However%2C%20I%20want%20to%20get%20the%20minimum%20time%20instead%20of%20the%20%23value!.%20Is%20there%20a%20way%20I%20can%20make%20this%20work%20using%20the%20MINIF%20function%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2632789%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2632847%22%20slang%3D%22en-US%22%3ERe%3A%20MINIFS%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2632847%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1124346%22%20target%3D%22_blank%22%3E%40sp3124%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAs%20an%20%3CSTRONG%3Earray%20formula%3C%2FSTRONG%3E%20confirmed%20with%20%3CSTRONG%3ECtrl%2BShift%2BEnter%3C%2FSTRONG%3E%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DMIN(IF((%24A%242%3A%24A%24100%3DD2)*ISNUMBER(%24B%242%3A%24B%24100)%2C%24B%242%3A%24B%24100))%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Ewhere%20D2%20is%20a%20cell%20containing%20the%20ID%20you're%20looking%20for.%3C%2FP%3E%0A%3CP%3EAdjust%20the%20ranges%20as%20needed.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Hello,

I have data in the below form :

ID Time
108/04/21 23:11:00
108/04/21 22:49:00
1#VALUE!

 

When I use the Minif function to get the min time for this id, I am getting the answer as #Value!. However, I want to get the minimum time instead of the #value!. Is there a way I can make this work using the MINIF function?

1 Reply
best response confirmed by sp3124 (Occasional Contributor)
Solution

@sp3124 

As an array formula confirmed with Ctrl+Shift+Enter:

 

=MIN(IF(($A$2:$A$100=D2)*ISNUMBER($B$2:$B$100),$B$2:$B$100))

 

where D2 is a cell containing the ID you're looking for.

Adjust the ranges as needed.