Formula Help

cmarie1020

New Member
Joined
Aug 23, 2019
Messages
2
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

New Member
Joined
Jul 29, 2013
Messages
23
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,450
Office Version
365
Platform
Windows
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"}))
 
Last edited:

Forum statistics

Threads
1,082,639
Messages
5,366,708
Members
400,914
Latest member
anandkb

Some videos you may like

This Week's Hot Topics

Top