Summing values across multiple columns, searching for first column in range

[davidpfitz]

Can anyone help me with the following problem?

I have a data structure like below, except that it's a lot wider and goes from the previous Monday to 365 days out across the width of it. There are about 1200 rows of data.

11/5/2020
11/5/2020	12/5/2020	13/5/2020	14/5/2020	15/5
100%	50%	10%	0%	100%
50%	20%	0%	0%	50%

Currently, this formula searches the % data for the date in A1 and returns the sum of that column of data - 150%.

=SUM(INDEX(A3:D4,,MATCH(A1,A2:D2,0)))

However I need it so sum up the entire week (i.e. the date in A1 + the next 4 days to make a 5 day working week), so would want it to return 380%.

I know this would be simple if the exact range was known, but it's not - I want it parameterised by A1.

Or even more ideally, I would like to be able to give a start date and an end date and then be given the sum of all the data between those dates.

Anyone got experience doing something like this?

Thank you.

 

[gaz_chops]

Welcome to the forum.

Try

=SUMPRODUCT(($A$2:$E$2>=$A$1)*($A$2:$E$2<=$B$1)*(A3:E4))

 

[davidpfitz]

Welcome to the forum.

Try

=SUMPRODUCT(($A$2:$E$2>=$A$1)*($A$2:$E$2<=$B$1)*(A3:E4))

View attachment 13593

View attachment 13594

Thank you - I have been struggling with that all day. That is such a nice and elegant way of doing it. I am massively grateful and will now have to go away and learn how SUMPRODUCT works because that is insanely useful.

Thanks again - you have saved me from (probably futilely) working really late tonight.

David.

 

[gaz_chops]

You're welcome, yes sumproduct is very useful so well worth getting to grips with.

Stay safe

Gaz