Gathering information by year

[Thomas Comtois]

I have a excel vehicle tracking system that uses log sheets entered by trip that has a date associated with each entry. This includes kilometers driven for that date and for what vehicle. I have the ability to change the year and month on a report tab that will produce the kilometers per vehicle with that particular year and month. I would like to get the same kilometer information for a entire year not just one month but have not found a formula that works yet. This is my first posting on this forum so i'm sure my terminology is a little weird. If uploading a screen shot would be helpful can I do that here?
Thanks for the help
Thomas

 

[tyija1995]

Hi, welcome to the forums!

Maybe something like this?

[TABLE="class: grid, width: 750"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Distance (KM)[/TD]
[TD]Date[/TD]
[TD]Vehicle[/TD]
[TD][/TD]
[TD]Yearly KM[/TD]
[TD]2019[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]88.7[/TD]
[TD]01/04/2019[/TD]
[TD]Audi TT[/TD]
[TD][/TD]
[TD]Audi TT[/TD]
[TD]143.8[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]109.2[/TD]
[TD]09/05/2019[/TD]
[TD]Vauxhall Astra[/TD]
[TD][/TD]
[TD]Vauxhall Astra[/TD]
[TD]288.7[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]55.1[/TD]
[TD]15/06/2019[/TD]
[TD]Audi TT[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]179.5[/TD]
[TD]01/09/2019[/TD]
[TD]Vauxhall Astra[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Where F2 is given by the formula (and then dragged down Col F)

=SUMPRODUCT((YEAR($B$2:$B$5)=$F$1)*(E2=$C$2:$C$5)*($A$2:$A$5))

This formula says to look at the date and match it against 2019 in F1, then to match the car model in column E to the vehicle data in col C, and finally return the sum of the distances in column A that adhere to the first 2 conditions.

 

[Thomas Comtois]

Thank you tyija1995
this worked great for my basic report thank you very much but I have one that is a lot more complicated but needing the same yearly information.
I will try to describe it the best I can. I have a table with a tab called vehicle input this is where I enter data that has columns for date, vehicle name, kilometers driven, fuel purchased. I have a report tab that has two cells one for the year and one for the month that I can change the month and year to give me the information on the vehicle input tab. If I change the either the year or the month the information from the tab called vehicle is gathered and entered into my report tab accordingly. I want to the whole years information not just the month in that year. Here is the formula used to get the monthly information
=DATE(F8,MONTH(DATEVALUE(H8&" 1"))*1,1)
=DATE(F8,MONTH(DATEVALUE(H8&" 1 ))+1,1)-1
I don't know if this will make sense to you but I appreciate your trying to help

 

[tyija1995]

Hi,

I assume those two formulas you wrote result in the start and end date of a given month? (month in cell H8)

They can be rewritten as
=DATE(F8,MONTH("1/"&H8),1) for first day of month
=DATE(F8,MONTH("1/"&H8)+1,1)-1 for last day of month OR you could just put =EOMONTH(*reference first formula cell*, 0)

But if you want the entire year (say 2019) can you just do =DATE(F8, 1, 1) and =DATE(F8, 12, 31)

From there it's hard to see what you need to do - I would probably need a screenshot or something to work with, someone else might be able to go further though!

 

[Thomas Comtois]

tyija1995
I dont know if my last message got thru before I lost internet but thank you for all your help both of them work like a charm. I appreciate your help greatly