OK i need help with the if function.

[Guest]

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.

 

[Pete_UK]

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

Put this in B16 and copy across to F16.

Hope this helps.

Pete

 

[Ron Rosenfeld]

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

 

[Harlan Grove]

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?