Google Sheets – Query Minimum value for each month

I would approach it a different way, using ‘FILTER’ rather than ‘QUERY’. Assuming that your dates and amounts begin in D2 and E2 respectively, with headers in D1 and E1, try the following formula:


First, headers are generated (which can be changed within the formula as desired).

This — SORT(FILTER({DATE(YEAR(D2:D),MONTH(D2:D),1),E2:E},D2:D<>""),2,1) — will convert all dates to the first of that month and then sort by amount in ascending order, leaving the minimum amounts near the top and, therefore, the first to be found by the VLOOKUP.

FILTER then returns the YEAR, MONTH, DAY and amount only for rows where looking up the date-converted-to-first-of-month for that row in the SORT array returns the same amount as is listed in Col E for that row.

CLICK HERE to find out more related problems solutions.

Leave a Comment

Your email address will not be published.

Scroll to Top