Forum Discussion

Copper Contributor

Aug 20, 2020

Solved

Excel Formula needed

P P F P5 P3 P3 P14 P13 P13 P-3 is there a formula that helps me recognize and calculate the total amount P5+P3+P3+P14+P13+P13 = 51 Hrs That only recognize the positive & the lett...

excel

Formulas and Functions

SergeiBaklan

Aug 20, 2020

Fayezsamea

Sorry, missed that

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))

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)
  - Fayezsamea
    Copper Contributor
    Aug 20, 2020
    HiSergeiBaklan
    
    thank you so much, what if I want only the positive value
    P3 P3 P3 P3 P P-3 P13
    
    I want to calculate only the value more than zero and another one with the negative values only