Sum Large values along with Count Condition - Array Formula

Dear Friends,

I request your kind support to solve this. I have a data in O2:T2, like this:

Please Login or Register  to view this content.

I would like to sum large 4 values among 6 cells. For this, I used this ARRAY formula:

Please Login or Register  to view this content.

It works well, if minimum 4 values are entered between O2:T2. However, if there are less than 4 values, i.e. if only 2 or 3 values are entered in O2:T2, then #NUM error appears.

May I know how to avoid this error?

Request your kind help / suggestion.

Hi,

What do you want if there are less than 4 values? The minimum of those?

Regards

1 way could be using multiply if...

=IF(COUNT(O2:T2)>3,SUM(LARGE(O2:T2,{1,2,3,4})),IF(COUNT(O2:T2)>2,SUM(LARGE(O2:T2,{1,2,3}))))

Dear Sir,

Exactly I want to sum the LARGE 4 values from the range O2:T2. If there are no value entered in the range or less than 4 values in the range, then also the sum should be done without showing any error.

Hope to get the positive reply, sir.

Try

=IF(COUNT(O2:T2)>0,SUMPRODUCT(LARGE(O2:T2,ROW(INDIRECT("1:"&MIN(COUNT(O2:T2),4))))),"")

Hi,

=SUM(LARGE(O2:T2,ROW(INDIRECT("1:"&MIN(4,COUNT(O2:T2))))))

Array again.

Regards

Dear Ace_XL & Fotis 1991,

Thanks for your kind & swift response. Though both solves the purpose, the use of SUMPRODUCT formula provided by Ace_XL is easy to use.

Thanks for your sparing time in helping me.