Select multiple cells to perform product

[Marcrg]

Hello



Help!



Is there a formula/function that will enable me to find the product of all the values between two of the dates. for example, in the table below I may need it for from 2020 to 2022 so I'll need a formula that will give me 108.6*109.4*117.1.

Date	Value
01/03/2022	117.1
01/03/2021	109.4
01/03/2020	108.6
01/03/2019	107
01/03/2018	105
01/03/2017	102.5
01/03/2016	100.2
01/03/2015	99.7
01/03/2014	99.7
01/03/2013	98.1
01/03/2012	95.4

I effectively need something where I can tell it the two dates, and it will give me the product of all the values between those two dates. I thought it would be as easy as using the CELL function to get the cell names into the PRODUCT function but I can't seem to get that to work



Sorry if I'm not making this clear - any help will be greatly appreciated!



Thanks!

 

[Legacy 143009]

That will give the sum, not the product.

Yess

OK then my suggestion will be:

=PRODUCT(IF((YEAR(A2:A12)>=2020)*(YEAR(A2:A12)<=2022),B2:B12))

But this is an array formula. Just hitting Enter is not enough.
Paste the code and press Ctrl+Shift+Enter together.

 

[Marcrg]

Yess

OK then my suggestion will be:

=PRODUCT(IF((YEAR(A2:A12)>=2020)*(YEAR(A2:A12)<=2022),B2:B12))

But this is an array formula. Just hitting Enter is not enough.
Paste the code and press Ctrl+Shift+Enter together.

That works! Thank you so much

 

[Fluff]

If that works did you put the two years into E2 & E3 like I showed with my formula?

 

[Legacy 143009]

If that works did you put the two years into E2 & E3 like I showed with my formula?

Yes you can use references instead of putting values directly in the formula as @Fluff suggested.

 

[Marcrg]

If that works did you put the two years into E2 & E3 like I showed with my formula?

I did - and it was clearly working for you but something here didn't like it!

 

[Fluff]

Did you just put the year or an actual date?

 

[Marcrg]

Did you just put the year or an actual date?

just the year

 

[Fluff]

Odd, but at least you have a working solution.