Formula Help

[cmarie1020]

Hello,

I need some help with creating a formula.

Basically, I am counting up all the letters in a row (P, F, U, B, S) and then I want to minus from the calculation the count of (L) in the row … however, if L = or > zero, I want the cell to be 0 .

my current formula

=COUNTA(J5:AN5)-COUNTIF(J5:AN5, "L")


this is a spreadsheet tracking different types of absences but also lates. And I just want the calculation to produce the total absence types and disregard the lates entirely.

Thank you!

 

[bvbull200]

Are you saying that if "L" doesn't appear in any cells across that row, that the result should be 0?

Does this do what you're looking for?

=IF(COUNTIF(J5:AN5, "L")=0,0,COUNTA(J5:AN5)-COUNTIF(J5:AN5, "L"))

 

[Fluff]

Hi & welcome to MrExcel
Not sure I understand the bit about L= or > zero, but this will count all the P,F,U,B & Ss
=SUM(COUNTIF(J5:AN5,{"P","F","U","B","S"}))

 

[cmarie1020]

Are you saying that if "L" doesn't appear in any cells across that row, that the result should be 0?

Does this do what you're looking for?

=IF(COUNTIF(J5:AN5, "L")=0,0,COUNTA(J5:AN5)-COUNTIF(J5:AN5, "L"))

That works!! Thank you!!

 

[bvbull200]

That works!! Thank you!!

My pleasure!