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

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