I have the following formula for adding only the highest four numbers out of a row of six,=SUM(MAX(LARGE(B8:G8,1),0),MAX(LARGE(B8:G8,2),0),MAX(LARGE(B8:G8,3),0), MAX(LARGE(B8:G8,4),0)) but when only 3 numbers are filled in does not work unless a zero is inserted in at least one other cell in group, I thought that the ),0)) would show empty cell as zero, any suggestions would be most welcom
=IFERROR(SUMPRODUCT(LARGE(B8:G8,ROW(INDIRECT("1:"&MIN(COUNT(B8:G8),4))))),0)
Try this
Samba
Say thanks to those who have helped you by clicking Add Reputation star.
Hi there. try this out, instead.
=SUMPRODUCT(LARGE(B8:G8,IF(COUNT(B8:G8)=1,{1},IF(COUNT(B8:G8)=2,{1,2},IF(COUNT(B8:G8)=3,{1,2,3},{1,2,3,4})))))
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
Try this version
=IFERROR(SUM(LARGE(B8:G8,{1,2,3,4})),SUM(B8:G8))
It sums the top 4.....unless that produces an error, in which case it sums all of the numbers
Audere est facere
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks