Sum Large values along with Count Condition - Array Formula

[acsishere]

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.

[XOR LX]

Hi,

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

Regards

[Fotis1991]

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}))))

[acsishere]

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.

[Ace_XL]

Try

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

[XOR LX]

Hi,

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

Array again.

Regards

[acsishere]

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.

[vijay_prabu_cwa]

Hi Friends,

I am very new to this Website, Joined few hours back. I am average in Excel Formula and would to develop it from here.

Could anyone of you explain me how the Row(Indirect()) function works here.
=IF(COUNT(O2:T2)>0,SUMPRODUCT(LARGE(O2:T2,ROW(INDIRECT("1:"&MIN(COUNT(O2:T2),4))))),"")


Thanks
Vijay