Sum of highest 4 consecutive numbers and define the 4 numbers

foxfire74

New Member
Joined
Nov 9, 2019
Messages
1
Hi everyone.

I have a number spreadsheet with 20 columns /100 rows of numbers. I need to get the sum of the highest 4 CONSECUTIVE numbers of each columns. I have found two formula to get the sum:

=SUMPRODUCT(MAX(D3:D95+D4:D96+D5:D97+D6:D98))

or

=MAX(INDEX(D3:D95+D4:D96+D5:D97+D6:D98,0))

1). Both of them work but can someone explain the difference between them?

2). I would also like to define or highlight the cells of the four consecutive numbers that totaled the sum from. Is there a way to tell which four numbers the formula calculated? I thought maybe using Conditional Formatting but I haven't been able to figure out how.

Any advise or guidance would be greatly appreciated.

Thanks in advance,
Frankie
 

Forum statistics

Threads
1,078,367
Messages
5,339,783
Members
399,323
Latest member
letitiaysk

Some videos you may like

This Week's Hot Topics

Top