Forum Discussion

Fayezsamea's avatar
Fayezsamea
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's avatar
    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))
Fayezsamea's avatar
Fayezsamea
Copper Contributor
Aug 20, 2020

HiSergeiBaklan 

 

thank you so much, what if I want only the positive value 

P3P3P3P3PP-3P13

 

I want to calculate only the value more than zero and another one with the negative values only

 

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
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))

Resources