Finding top four scores, need to insert zero
 

I'm using this formula to determine the top four scores of a competitions
series:

=SUMPRODUCT(LARGE(F2:K2,ROW(INDIRECT("1:4"))))

It works great, but not everyone has done four competitions and since those
cells empty, I get a #NUM! error.

Is there a way to insert zeros into the empty cells, or is there a better
formula?

Thanks!

Finding top four scores, need to insert zero
 

Try this:

=IF(COUNT(F2:K2)<4,SUM(F2:K2),SUM(LARGE(F2:K2,{1,2 ,3,4})))


"Raza" wrote:

I'm using this formula to determine the top four scores of a competitions
series:

=SUMPRODUCT(LARGE(F2:K2,ROW(INDIRECT("1:4"))))

It works great, but not everyone has done four competitions and since those
cells empty, I get a #NUM! error.

Is there a way to insert zeros into the empty cells, or is there a better
formula?

Thanks!

Finding top four scores, need to insert zero
 

Try

=SUMIF($F$2:$K$2,"="&LARGE($F$2:$K$2,4))

Hope this helps.

On Mar 10, 9:26 am, Raza wrote:
I'm using this formula to determine the top four scores of a competitions
series:

=SUMPRODUCT(LARGE(F2:K2,ROW(INDIRECT("1:4"))))

It works great, but not everyone has done four competitions and since those
cells empty, I get a #NUM! error.

Is there a way to insert zeros into the empty cells, or is there a better
formula?

Thanks!