Multiply an array based on date value entered.

[AlInVegas2]

In one cell, I'm trying to get a product if from the array below. If I enter a date of 7/1/23, the result is the product of the Factor column for all dates that are less than or equal to the date entered.
In the case of 7/1/23, the result would be 1*7*3 or 21. In the case of 1/1/24, it would be 1*7*3*9 or 189.... attempting to use SumProduct( or Product(if commands with little success.

Date	Factor
09/01/2020	1
12/11/2021	7
06/04/2022	3
07/03/2023	9
01/02/2024	4
10/15/2024	6

 

[Cubist]

Which version of Excel are you using?
Try:

Book1
	A	B	C	D	E
1	Date	Factor
2	9/1/2020	1		7/1/2023	21
3	12/11/2021	7		1/1/2024	189
4	6/4/2022	3
5	7/3/2023	9
6	1/2/2024	4
7	10/15/2024	6
Sheet2

Cell Formulas
Range	Formula
E2:E3	E2	=PRODUCT(IF($A$2:$A$7<=D2,$B$2:$B$7,1))

 

[AlInVegas2]

Which version of Excel are you using?
Try:

Book1
	A	B	C	D	E
1	Date	Factor
2	9/1/2020	1		7/1/2023	21
3	12/11/2021	7		1/1/2024	189
4	6/4/2022	3
5	7/3/2023	9
6	1/2/2024	4
7	10/15/2024	6
Sheet2

Cell Formulas
Range	Formula
E2:E3	E2	=PRODUCT(IF($A$2:$A$7<=D2,$B$2:$B$7,1))

I found the issue, I have an area defined that has blank cells in it for the comparative value. How can I get the same formula to ignore rows without data?

 

[Cubist]

I found the issue, I have an area defined that has blank cells in it for the comparative value.

Can you show what you mean by this?

Also, please answer which Excel version you are using. The optimal solution may vary.

 

[AlInVegas2]

Can you show what you mean by this?

Also, please answer which Excel version you are using. The optimal solution may vary.

I'm using office 365..

So in this table:

Date	Factor
09/01/2020	1
12/11/2021	7
06/04/2022	3
07/03/2023	9
01/02/2024	4
10/15/2024	6
10/15/2024	6
02/11/2021	4

SplitDate is the range of cells in the date column (including the blank ones). SplitFactor is that same in the factor column.

Using:

=PRODUCT(IF(SplitDate<=@TrDate,SplitFactor,1)) where @TrDate equals 12/11/21, the result is 0. Filling the table with values, the result is a product where the date value test holds true.

To fix this, I used Offset to count the number of dates entered.
=PRODUCT(IF(OFFSET(SplitDate,0,0,COUNT(SplitDate),1)<=@TrDate,OFFSET(SplitFactor,0,0,COUNT(SplitDate),1),1))

With @TrDate = 12/11/21, the result from the entire table below (blank cells included is 28, (1*7*4). So this works...

Now to complicate things.. I added another condition, whereby I could exclude certain rows from being included when they met the date criteria...

=PRODUCT(IF((OFFSET(SplitDate,0,0,COUNT(SplitDate),1)<=@TrDate)*(OFFSET(SplitExclude,0,0,COUNT(SplitDate),1)="")=1,OFFSET(SplitFactor,0,0,COUNT(SplitDate),1),1))

From the table below, with @TrDate = 12/11/21, the result was 7 (1*7) with the 4 excluded the product calculation. Appreciate your help in getting me closer.

Date	Factor	Exclude
09/01/2020	1
12/11/2021	7
06/04/2022	3
07/03/2023	9
01/02/2024	4
10/15/2024	6
10/15/2024	6
02/11/2021	4	P

 

[Cubist]

How about

Book1
	A	B	C	D	E
1	Date	Factor	Exclude
2	9/1/20	1		12/11/21	7
3	12/11/21	7		1/1/24	189
4	6/4/22	3
5	7/3/23	9
6	1/2/24	4
7	10/15/24	6
8	2/11/11	4	P
9
10
Sheet1

Cell Formulas
Range	Formula
E2:E3	E2	=PRODUCT(IF(($A$2:$A$10<=D2)*($A$2:$A$10<>"")*($C$2:$C$10<>"P"),$B$2:$B$10,1))

 

[AlInVegas2]

How about

Book1
	A	B	C	D	E
1	Date	Factor	Exclude
2	9/1/20	1		12/11/21	7
3	12/11/21	7		1/1/24	189
4	6/4/22	3
5	7/3/23	9
6	1/2/24	4
7	10/15/24	6
8	2/11/11	4	P
9
10
Sheet1

Cell Formulas
Range	Formula
E2:E3	E2	=PRODUCT(IF(($A$2:$A$10<=D2)*($A$2:$A$10<>"")*($C$2:$C$10<>"P"),$B$2:$B$10,1))

That is essentially what I have except the offsets allow me to have the amount of split data to be more dynamic. Appreciate your skewing me towards the answer.

 

[Cubist]

What does “split data” mean?

 

[Fluff]

I'm using office 365..

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)