Ignore cells that aren't numbers without using IsNumber-CodePudding

The formula in cell F3 is this

=SUMPRODUCT($A$4:$A$2000, F4:F2000,--($A$4:$A$2000>=0*F4:F2000>=0))

It multiplies Col A with Col F (and then col G, H, etc) and gives the result in row 3.

Some cells contain 'N/A' or 'tr'. I hoped that last >=0 condition would somehow just ignore them but alas. I'm hoping there is a simple of way of saying: ignore everything that isn't a number. I tried if(isnumber) but I was putting it everywhere because I didn't know exactly where it should go in this case, and all those if then conditions were getting so long!

Is there a simpler way? Or if I do have to use iserror (or if(ISNA)) and/or if(isnumber), can someone give me clue as to where they should go and if there is a way to avoid the if condition?

CodePudding user response：

use in F3 and drag to the right:

=INDEX(SUM(IFERROR($A4:$A*F4:F)))

or delete F3:I3 and use this only in F3:

=INDEX(TRANSPOSE(MMULT(TRANSPOSE(IFERROR($A4:$A*F4:I, 0)), SEQUENCE(ROWS(A4:A), 1, 1, 0))))

CodePudding user response：

In F3 try

=SUMPRODUCT($A$4:$A$2000, $A$4:$A$2000>=0, isnumber(F$4:F$2000), F$4:F$2000)

=SUM(FILTER($A$4:$A$2000*F$4:F$2000, $A$4:$A$2000>=0, isnumber(F$4:F$2000)))

and fill to the right for the other columns. See if that works?