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!  Reply With Quote

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"))  Reply With Quote

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"}))  Reply With Quote

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!!  Reply With Quote

5. Re: Formula Help Originally Posted by cmarie1020 That works!! Thank you!!
My pleasure!  Reply With Quote

User Tag List

Tags for this Thread

calculation, formula, lates, row, types  Posting Permissions

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