SOLVED
Home

Excel COUNTIF function not counting correctly

%3CLINGO-SUB%20id%3D%22lingo-sub-802371%22%20slang%3D%22en-US%22%3EExcel%20COUNTIF%20function%20not%20counting%20correctly%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-802371%22%20slang%3D%22en-US%22%3E%3CP%3EI've%20been%20using%20a%20sheet%20for%20a%20long%20time.%20Suddenly%20today%2C%20a%20simple%20COUNTIF%20function%20doesn't%20work%20any%20more.%20The%20formula%20is%20as%20simple%20as%20they%20get%3A%26nbsp%3B%3DCOUNTIF(AC23%3ASZ23%2C%22L%22).%20There%20are%20numerous%20'L's%20in%20the%20row%2C%20yet%20it%20returns%201.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThere%20are%20many%20other%20such%20countif%20functions%20counting%20data%20in%20the%20same%20row%2C%20such%20as%26nbsp%3B%3DCOUNTIF(AC23%3ASZ23%2C%22FDO%22)%2C%20which%20works%20perfectly.%20It%20looks%20at%20exactly%20the%20same%20data%20set%2C%20formatted%20in%20exactly%20the%20same%20manner.%20The%20cells%20are%20all%20'general'.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20can%20be%20wrong%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-802371%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-802578%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20COUNTIF%20function%20not%20counting%20correctly%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-802578%22%20slang%3D%22en-US%22%3EThere%20might%20be%20L%E2%80%99s%20with%20leading%20and%20trailing%20spaces.%20Try%20this%3A%3CBR%20%2F%3E%3DSUMPRODUCT(%E2%80%94(TRIM(AC23%3ASZ23)%3D%E2%80%9CL%E2%80%9D))%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-802718%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20COUNTIF%20function%20not%20counting%20correctly%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-802718%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20formula%20works%2C%20thanks.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20checked%2C%20and%20there%20%3CEM%3Ewere%3C%2FEM%3Etraining%20spaces%20behind%20the%20'L's.%20Upon%20investigation%20it%20turns%20out%20that%20my%20staff%20member%20typed%20in%20'leave'%20and%20then%20corrected%20it%20afterwards.%20Excel%20let%20the%20word%20'leave'%20reappear%20when%20he%20tried%20typing%20in%20'L'%2C%20so%20he%20made%20a%20space%20to%20get%20the%20'L'.%20And%20that%20'incapacitated'%20my%20formula.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-802795%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20COUNTIF%20function%20not%20counting%20correctly%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-802795%22%20slang%3D%22en-US%22%3EYou%E2%80%99re%20welcome!%3C%2FLINGO-BODY%3E
J_Uys123
New Contributor

I've been using a sheet for a long time. Suddenly today, a simple COUNTIF function doesn't work any more. The formula is as simple as they get: =COUNTIF(AC23:SZ23,"L"). There are numerous 'L's in the row, yet it returns 1.

 

There are many other such countif functions counting data in the same row, such as =COUNTIF(AC23:SZ23,"FDO"), which works perfectly. It looks at exactly the same data set, formatted in exactly the same manner. The cells are all 'general'.

 

What can be wrong?

3 Replies
Solution
There might be L’s with leading and trailing spaces. Try this:
=SUMPRODUCT(—(TRIM(AC23:SZ23)=“L”))

@Twifoo 

 

This formula works, thanks.

 

I checked, and there were training spaces behind the 'L's. Upon investigation it turns out that my staff member typed in 'leave' and then corrected it afterwards. Excel let the word 'leave' reappear when he tried typing in 'L', so he made a space to get the 'L'. And that 'incapacitated' my formula.

You’re welcome!
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies