# Formula for matching year value with date value and generating the result

1. ## Formula for matching year value with date value and generating the result

Hi gurus and experts,

I am trying to create a formula that will match year value from date and return the result for instance:

Date Value Year (Input) Value (Output needed by formula)
01/01/2016 367.1 2020 451.67
01/01/2017 386.7 2019 429.87
01/01/2018 410.95 2018 410.95
01/01/2019 429.87 2017 386.7
01/01/2020 451.67 2016 367.1

The output is my requirement in the fourth column.

Thank you
Roshan

2. ## Re: Formula for matching year value with date value and generating the result

Try this in D2:

=SUMIFS(B:B,A:A,">="&DATE(C2,1,1),A:A,"<="&DATE(C2,12,31))

Then copy down as required.

I see that all your dates are for the first of January, so if that is true for all your data then a simpler approach would be:

=SUMIF(A:A,DATE(C2,1,1),B:B)

Hope this helps.

Pete

3. ## Re: Formula for matching year value with date value and generating the result

Try:
Formula:
`Please Login or Register  to view this content.`

4. ## Re: Formula for matching year value with date value and generating the result

Many thanks @Pete_UK. That is fabulous.

Thank you
Roshan

5. ## Re: Formula for matching year value with date value and generating the result

@Pete: just too quick for me! I assumed, from the sample data, there was only one entry per year

6. ## Re: Formula for matching year value with date value and generating the result

Could be, Trevor, but then you could use VLOOKUP.

Pete

There are currently 1 users browsing this thread. (0 members and 1 guests)