Another one:

Array** entered:

=SUM(IF(ISNUMBER(CHOOSE({1,2,3,4,5,6},B20,D20,G20, B41,D41,G41)),CHOOSE({1,2,3,4,5,6},B20,D20,G20,B41 ,D41,G41)))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Harlan Grove" wrote in message
oups.com...
"Peo Sjoblom" wrote...
=SUMPRODUCT(SUMIF(INDIRECT({"B20","D20","G20","B 41","D41","G41"}),
"<=0"&999^99))

will work

In addition to being volatile, it's also rather inflexible if at some
later date the OP wants to move any of these cells, insert/delete rows/
columns, etc. Since this approach already involves listing each cell
separately, a more flexible, nonvolatile alternative would be

=SUMIF(B20,"<=9.9999999999999E307")+SUMIF(D20,"<=9 .9999999999999E307")
+SUMIF(G20,"<=9.9999999999999E307")+SUMIF(B41,"<=9 .9999999999999E307")
+SUMIF(D41,"<=9.9999999999999E307")+SUMIF(G41,"<=9 .9999999999999E307")

or define the name NUMS referring to ="<=9.99999999999999E307" and
change the formula to

=SUMIF(B20,NUMS)+SUMIF(D20,NUMS)+SUMIF(G20,NUMS)+S UMIF(B41,NUMS)
+SUMIF(D41,NUMS)+SUMIF(G41,NUMS)


however it is insane using a formula like this when all you have
to do is to fix the formula(s) with the #N/A in the first place.
...

Still quite true. Handling errors at the source is much easier in the
long run than handling them down stream.