Sum of last 5 values higher than zero

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
11,151
Office Version
2007
Platform
Windows
Try this array formula

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:76.04px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >AH</td><td >AI</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >241</td><td style="text-align:right; ">18.7</td><td > </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td ></td><td >Array Formula</td></tr><tr><td ></td><td >{=SUM(IF(COLUMN(C241:AG241)>=LARGE(IF(C241:AG241>0,COLUMN(C241:AG241)),5),C241:AG241))}</td></tr></table></td></tr></table>

Array formulas
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
11,151
Office Version
2007
Platform
Windows
I'm glad to help you. Thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,095,544
Messages
5,445,099
Members
405,316
Latest member
joaoamaro

This Week's Hot Topics

Top