SOLVED

# Excel Formula needed

Occasional Contributor

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

it would really be appreciated

5 Replies

# Re: Excel Formula needed

``=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

# Re: Excel Formula needed

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

# Re: Excel Formula needed

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)

# Re: Excel Formula needed

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

best response confirmed by Fayezsamea (Occasional Contributor)
Solution

# Re: Excel Formula needed

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