SOLVED

Sum all cells with text EXCEPT with letter "D"

Copper 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.

 

Lucy_McMahon_1-1663966112019.png

 

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 (Copper Contributor)
Solution

@Lucy_McMahon 

 

=[@[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.
1 best response

Accepted Solutions
best response confirmed by Lucy_McMahon (Copper Contributor)
Solution

@Lucy_McMahon 

 

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

View solution in original post