Positive only:
=SUMPRODUCT(IFERROR(--SUBSTITUTE(A3:J3,"P",""),0)*(IFERROR(--SUBSTITUTE(A3:J3,"P",""),0)>0))

Negative only:
=SUMPRODUCT(IFERROR(--SUBSTITUTE(A3:J3,"P",""),0)*(IFERROR(--SUBSTITUTE(A3:J3,"P",""),0)<0))

5 Replies

ExcelExciting
MVP
Aug 20, 2020
Hi Fayezsamea

Please find below construction of the formula which help you to get the desired result.

=SUMPRODUCT(IF(IF(LEFT(A3:J3,1)="P",IFERROR(VALUE(MID(A3:J3,2,5)),0),0)>=0,IF(MID(A3:J3,1,1)="P",IFERROR(VALUE(MID(A3:J3,2,5)),0),0),0))

Regards, Faraz Shaikh | MCT, MIE, MOS Master, Excel Expert
If you find the above solution resolved your query don't forget mark as Official/Best Answer to help the other members find it more
- Fayezsamea
  Copper Contributor
  Aug 20, 2020
  ExcelExciting
  
  i am trying to make it work but it doesn't seem to work for me
  - SergeiBaklan
    Diamond Contributor
    Aug 20, 2020
    Fayezsamea
    
    As a variant
    
    =SUM(IFERROR(--SUBSTITUTE(A3:J3,"P",""),0))
    
    If your Excel doesn't support dynamic arrays when array formula (Ctrl+Shift+Enter to enter)