Aug 20 2020 01:03 AM
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
Aug 20 2020 01:49 AM - edited Aug 20 2020 02:00 AM
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
Aug 20 2020 09:19 AM
Aug 20 2020 09:27 AM
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)
Aug 20 2020 09:57 AM
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
Aug 20 2020 10:04 AM
SolutionSorry, 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))
Aug 20 2020 10:04 AM
SolutionSorry, 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))