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!

 

[Fluff]

Hi & welcome to MrExcel.
How about

Fluff.xlsm
	A	B	C	D	E	F
1	Date	Value
2	01/03/2022	117.1			2020	1391246
3	01/03/2021	109.4			2022
4	01/03/2020	108.6
5	01/03/2019	107
6	01/03/2018	105
7	01/03/2017	102.5
8	01/03/2016	100.2
9	01/03/2015	99.7
10	01/03/2014	99.7
11	01/03/2013	98.1
12	01/03/2012	95.4
13
Code

Cell Formulas
Range	Formula
F2	F2	=PRODUCT(FILTER(B2:B12,(YEAR(A2:A12)>=E2)*(YEAR(A2:A12)<=E3)))

 

[Legacy 143009]

=SUMPRODUCT(--(YEAR(A2:A12)>=2020)*--(YEAR(A2:A12)<=2022),B2:B12)

 

[Fluff]

That will give the sum, not the product.

 

[Marcrg]

Hi,

Thanks both for looking at it for me.

The SUMPRODUCT formula does indeed give the sum - but I can't get Fluff's PRODUCT formula to work, it gives me the #CALC! error. Is my version of excel missing something?

Thanks!

 

[Fluff]

#calc suggests that the filter didn't find anything.
If you select the column with the dates & change the format to General, do you still see dates or numbers

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Select multiple cells to perform product
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[Marcrg]

#calc suggests that the filter didn't find anything.
If you select the column with the dates & change the format to General, do you still see dates or numbers

I see numbers

 

[Fluff]

Ok, that's fine. Can you post some sample data, including the formula.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Marcrg]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Select multiple cells to perform product
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

Apologies