Find first cell value of current year

[Leroy49]

Hello. First post. I have searched and searched but can't figure out how to solve my problem. I am tracking expenses of my vehicles. What I want to do is find the first occurrence of the "Mileage" figure for the year. That way, 2021 and other years will also have the first value found. Same goes for the last entry of the year.

I have been using Vlookup and Lookup to find the values, and those formulas are working correctly. But, they are searching for the entire list of entries, and so far, it is just for the year 2020.

So, How do I find the mileage for just this year (2020)? The formula should also work when I create columns for the next year. I have attached a sample of the expense sheet with the formula in question highlighted in blue.

Note: I am using 2016 Home and student version so the latest functions like MINIFS don't work.

All help and advice is appreciated.
Thanks
Mike

[MarvinP]

Hi Leroy and welcome to the forum,

Excel has this tool called Pivot Tables that can break numbers out by day or month or quarter or year and do totals and subtotals on them AND so much MORE.
Uber Stats.xlsm
See the attached and search on Pivot Tables to see what they might do for you.

[MarvinP]

Hey Leroy,
I played with the Pivot a little (no formulas needed just drag and drop and right click on cells to display other stuff) See a new pivot.
Uber Stats Mileage by Vehicle.xlsm
Also I needed to repeat the Vehicle in Col C so it was "GOOD" table data. It is bad to leave blanks in a cell when you are trying to filter or sort by that data.

[Leroy49]

Thanks. I have never used pivot tables.

All I really need is the mileage reading (from the odometer) from the first occurrence of the year. In the example sheet for the 15 Traverse, Vlookup found the mileage number in cell M20. Is there a way to modify the existing Vlookup formula to include the year? Something like the SUMIFS formula where the year is calculated (greater than 1/1/2020 and less than 12/31/2020) as used in cell I3 for the "gas" total. Note: The dates are in a range called Expense_Dates.

I was hoping to use MINIFS and MAXIFS, but my version of Excel doesn't allow it.

[MarvinP]

Hey Leroy,

Pivot Tables has a "Min" feature where you don't need to show the Sum of Miles or Average of Miles, you can show the Min Odo Reading by date. No Formulas. I thought you could do MinIfs() . Sorry but see the attached Pivot Table
Uber Start Yr Odo Reading.xlsm
If you learned Pivots, you could do so much more with your data!!
If you demand it, I could most likely write a MinIFS() formula for each vehicle but I'd need to use a helper.

[CARACALLA]

D3=SUMPRODUCT(($C$16:$C$63=$A$3)*(YEAR($B$16:$B$63)=I$2)*(INDEX($B$16:$T$63,0,MATCH($B3,$B$15:$T$15,0)))*($D$16:$D$63=LEFT($B3,7)))

Copy down D3 -D4

[Leroy49]

Thanks for the help. I will do some research and learn more about pivot tables.

[Leroy49]

D3=SUMPRODUCT(($C$16:$C$63=$A$3)*(YEAR($B$16:$B$63)=I$2)*(INDEX($B$16:$T$63,0,MATCH($B3,$B$15:$T$15,0)))*($D$16:$D$63=LEFT($B3,7)))

Copy down D3 -D4

Thanks. I will check that out.

[CARACALLA]

YEAR 2020

MIN Milage for 15 Traverse


D3=AGGREGATE(15,6,INDEX($B$16:$T$63,0,MATCH(B3,$B$15:$T$15,0))/(YEAR($B$16:$B$63)=$I$2)/($C$16:$C$63=$A$3)/($D$16:$D$63=LEFT(B3,7)),1)

MAX Milage for 15 Traverse

D4=AGGREGATE(14,6,INDEX($B$16:$T$63,0,MATCH($B4,$B$15:$T$15,0))/(YEAR($B$16:$B$63)=$I$2)/($C$16:$C$63=$A$3)/($D$16:$D$63=LEFT(B4,7)),1)


The previous formula gave the sum not to consider it

[Leroy49]

YEAR 2020

MIN Milage for 15 Traverse


D3=AGGREGATE(15,6,INDEX($B$16:$T$63,0,MATCH(B3,$B$15:$T$15,0))/(YEAR($B$16:$B$63)=$I$2)/($C$16:$C$63=$A$3)/($D$16:$D$63=LEFT(B3,7)),1)

MAX Milage for 15 Traverse

D4=AGGREGATE(14,6,INDEX($B$16:$T$63,0,MATCH($B4,$B$15:$T$15,0))/(YEAR($B$16:$B$63)=$I$2)/($C$16:$C$63=$A$3)/($D$16:$D$63=LEFT(B4,7)),1)


The previous formula gave the sum not to consider it

Thanks. I saw the total. I will check out these formulas. I really appreciate the help. I'm just an old retired guy that likes Excel...still feel like a rookie.

EDIT:
WOW! Perfect. Thank you so much. I am going to study those two formulas and try to learn more about how the result was obtained.