#NA #Value SUMIF

[indygo]

hi all,


How can I improve both formulas to work even if #N/A and #Value ! appear in the column?


=SUM(SUMIF(E3:F655,{"W","L"},AO3:AP655))
=SUM(SUMIF(AR3:AS655,{"<0",">0"}))-AN1



thanks

 

[pgc01]

Hi

In which columns will the error values appear?

 

[Aladin Akyurek]

Perhaps...

=SUM(SUMIFS(AO3:AP655,E3:F655,{"W","L"},AO3:AP655,"<9.99E+307"))

=SUM(SUMIF(AR3:AS655,{"<0",">0"}))-IF(ISNUMBER(AN1),AN1,0)

 

[indygo]

Sorry!

=SUM(SUMIF(E3:F655,{"W","L"},AO3:AP655)) - in column AO and AP
=SUM(SUMIF(AR3:AS655,{"<0",">0"}))-AN1 - in column AR and AS

 

[indygo]

Perhaps...

=SUM(SUMIFS(AO3:AP655,E3:F655,{"W","L"},AO3:AP655,"<9.99E+307"))

=SUM(SUMIF(AR3:AS655,{"<0",">0"}))-IF(ISNUMBER(AN1),AN1,0)

This seems to be working!

thank you

 

[Aladin Akyurek]

This seems to be working!

thank you

You are welcome.