Search code examples
excelexcel-formulaarray-formulas

Find minimum value in 2nd column of month in first column

Column A: dates, Column B: values

I need to find the minimum value of the given month

e.g.

date value
Jan 2 2024 5
Feb 7 2024 33
Mar 5 2024 7
Jan 3 2024 23
Mar 20 2024 18
Mar 20 2022 1

given: March 2024

Result of formula: 7

Solution

  • Try using the following formula:

    enter image description here

    =MIN(TOCOL(B2:B7/(TEXT(E2,"me")=TEXT(A2:A7,"me")),2))

    Or, Using AGGREGATE()

    =AGGREGATE(15,6,B2:B7/(TEXT(E2,"me")=TEXT(A2:A7,"me")),1)

    Or using MINIFS()

    =MINIFS(B2:B7,A2:A7,">="&E2,A2:A7,"<="&EOMONTH(E2,0))

    If those dates are texts formatted with spaces in between, then try the followings as well:

    enter image description here

    =AGGREGATE(15,6,B2:B7/(TEXT(--SUBSTITUTE(A2:A7," ",", ",2),"em")=TEXT(--E2,"em")),1)