# Thread: Formula Help Thanks: 0 Likes:  1 Post #5330796 (1)

1. ## Formula Help

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!

2. ## Re: Formula Help

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

3. ## Re: Formula Help

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"}))

4. ## Re: Formula Help

Originally Posted by 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"))

That works!! Thank you!!

5. ## Re: Formula Help

Originally Posted by cmarie1020
That works!! Thank you!!
My pleasure!

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•