Sum all cells with text EXCEPT with letter "D"

Occasional Contributor

Calculating the M column for hours worked, I have a formula that will sum all amounts in cells in columns B:K with text to subtract them from the L column. Hours that remain are shown as hours worked.


i.e. 80 scheduled hours -P8 -A8 - S8 = 56 hours worked. 


The formula is:


=[@[Scheduled Hours]]-SUM(IFERROR(--MID(Table27[@[M1]:[F2]],2,99),0))


But I need it to ignore the cells with "D" in them and not include them in the sum.




So the last row will still show "D8" for the F2 column but it will not subtract from the total, and worked hours would be 24. 


2 Replies
best response confirmed by Lucy_McMahon (Occasional Contributor)



=[@[Scheduled Hours]]-SUM(IFERROR(--IF(LEFT(Table27[@[M1]:[F2]])<>"D",MID(Table27[@[M1]:[F2]],2,99)),0))

Thank you! I was entering the <>"D" but it wasn't working and I knew I was missing a piece.