Pls help me. My current column formula is =SUM(LARGE(B8:B15,{1,2,3,4,5,6})).
that get's me the top 6 values i need, but only if values are entered. If I
do not put values in cells 8:15 i get #NUM! as a result. This formula is
being used in 5 columns (b,c,d,e,f). The totals are in cell 16 for each
column. I need to get the value 0 WHILE LEAVING THE CELLS EMPTY because I
grand total the totals by using the formula =SUM(B16:F16). That formula
won't unless it has at least a value of 0 for each column, or it returns the
#NUM! also.

Try this then:

=IF(ISERROR(SUM(LARGE(B8:B15,{1,2,3,4,5,6}))),0,SU M(LARGE(B8:B15,
{1,2,3,4,5,6})))

Put this in B16 and copy across to F16.

Hope this helps.

Pete

On May 15, 8:16 am, TheDrip wrote:
Pls help me. My current column formula is =SUM(LARGE(B8:B15,{1,2,3,4,5,6})).
that get's me the top 6 values i need, but only if values are entered. If I
do not put values in cells 8:15 i get #NUM! as a result. This formula is
being used in 5 columns (b,c,d,e,f). The totals are in cell 16 for each
column. I need to get the value 0 WHILE LEAVING THE CELLS EMPTY because I
grand total the totals by using the formula =SUM(B16:F16). That formula
won't unless it has at least a value of 0 for each column, or it returns the
#NUM! also.

On Tue, 15 May 2007 00:16:01 -0700, TheDrip
wrote:

Pls help me. My current column formula is =SUM(LARGE(B8:B15,{1,2,3,4,5,6})).
that get's me the top 6 values i need, but only if values are entered. If I
do not put values in cells 8:15 i get #NUM! as a result. This formula is
being used in 5 columns (b,c,d,e,f). The totals are in cell 16 for each
column. I need to get the value 0 WHILE LEAVING THE CELLS EMPTY because I
grand total the totals by using the formula =SUM(B16:F16). That formula
won't unless it has at least a value of 0 for each column, or it returns the
#NUM! also.

If there are fewer than six entries, do you want to sum the entries that are
present? Or do you want a Zero?

If the former, then:

=IF(COUNT(B8:B15)=0,0,SUMPRODUCT(LARGE(B8:B15,
ROW(INDIRECT("1:"&MIN(6,COUNT(B8:B15)))))))

If the latter, then:

=IF(COUNT(B8:B15)<6,0,SUM(LARGE(B8:B15,{1,2,3,4,5, 6})))


--ron

"Pete_UK" wrote...
Try this then:

=IF(ISERROR(SUM(LARGE(B8:B15,{1,2,3,4,5,6}))),0 ,
SUM(LARGE(B8:B15,{1,2,3,4,5,6})))
....

That works, but the condition does way more than it needs to. How about

=IF(COUNT(B8:B15)=6,SUM(LARGE(B8:B15,{1,2,3,4,5,6 })),0)

which won't mask error values, if any, in B8:B15?