Sumproduct indirect array?

[Lidget]

Hi,
I made a formula to sum values in different columns on each row, based on number of days past today which is stored in c:4:c9 and the following works:
=SUM(INDIRECT(ADDRESS(ROW(C4:C9),C4:C9+MATCH(TODAY(),2:2,0),4)))

I now want to duplicate this to sum all the values in this array multiplied by a value in e:4:e9 but

=SUMPRODUCT(INDIRECT(ADDRESS(ROW(C4:C9),C4:C9+MATCH(TODAY(),2:2,0),4))E4:E9)
Is coming out zero no matter what the values in the target cells.

Any advice greatly appreciated

 

[Fluff]

Can you post some sample data.

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.

 

[Lidget]

Can you post some sample data.

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.

Sadly not, it's on work laptop and their internet blocks pretty much anything going in or out

 

[Fluff]

In that case can you just copy/paste some sample data.

 

[Lidget]

In that case can you just copy/paste some sample data.

Knocked this up on my mobile so will see how it pastes in...

				16/6/2022	15/6/2022	14/6/2022	13/6/2022	12/6/2022	10/6/2022
	tasks	days to do it	minutes per thing
	thing 1	3	12				1
	thing 2	4	6					2
	thing 3	5	8						3
	thing 4	5	20						1
	thing 5	1	30		2
	thing 6	2	2			3

 

[Lidget]

Knocked this up on my mobile so will see how it pastes in...

				16/6/2022	15/6/2022	14/6/2022	13/6/2022	12/6/2022	10/6/2022
	tasks	days to do it	minutes per thing
	thing 1	3	12				1
	thing 2	4	6					2
	thing 3	5	8						3
	thing 4	5	20						1
	thing 5	1	30		2
	thing 6	2	2			3

Didn't go in great, tasks are B and dates are 2 for reference.
The sum formula added up all the numbers but sumproduct gives zero when multiplying but minutes array

 

[Fluff]

Thanks for that I can now see what your formula is doing, unfortunately I cannot see anyway to change it so that it multiplies the values by the time.
Hopefully somebody else will step in.

 

[Fluff]

Change of plan, is this what you want

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1
2					16/06/2022	15/06/2022	14/06/2022	13/06/2022	12/06/2022	10/06/2022
3		tasks	days to do it	minutes per thing
4		thing 1	3	12				1				134
5		thing 2	4	6					2
6		thing 3	5	8						3
7		thing 4	5	20						1
8		thing 5	1	30		2
9		thing 6	2	2			3
Database

Cell Formulas
Range	Formula
L4	L4	=SUMPRODUCT(INDEX(E4:J9,ROW(C4:C9)-ROW(C4)+1,MATCH(TODAY(),E2:J2,0)+C4:C9),D4:D9)

 

[Lidget]

Absolutely perfect, thank you!

 

[Fluff]

You're welcome & thanks for the feedback.