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
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
Try using the following formula:
=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:
=AGGREGATE(15,6,B2:B7/(TEXT(--SUBSTITUTE(A2:A7," ",", ",2),"em")=TEXT(--E2,"em")),1)
