Excel Formula needed

[Fayezsamea]

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 letter P (for present) 

it would really be appreciated

[#2]

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

 

[#3]

@Faraz Shaikh 

i am trying to make it work but it doesn't seem to work for me

[#4]

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

[#5]

Hi@Sergei Baklan 

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

[#6 · Accepted solution]

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