CODEWITHSUNDEEP
excelexcel-formulaarray-formulas
Wannes
Wannes

Reputation: 43

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

Upvotes: 0

Views: 47

Answers (1)

Mayukh Bhattacharya
Mayukh Bhattacharya

Reputation: 27263

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)

Upvotes: 3

Related Questions