[Solved] COUNTIF does not differentiate upper/lower case

[Geoff T]

=IF(COUNTIF($A$1:$A$12;I1)>0;0;1) [in cells G2 TO K2 is used to check to see if a character in a cell [Say I1] is also in a range.[A1:A12 generated randomly]
If true / returns 0 ; if false / returns 1.
As you see, COUNTIF does not differentiate between upper and lower case.

Options/Calc/calculate /case sensitive is checked on.

countif.jpg (74.78 KiB) Viewed 2391 times

Is there a way to differentiate between upper and lower case?
It also returns the incorrect answer when looking to verify that characters like <, >, = are in the range

Edit: Changed subject, was COUNTIF not working  Make your post understandable by others  -- MrProgrammer, forum moderator

[karolus]

Code: Select all

=SUM($A1:$A11=I$1)

ctrl ⇑ enter

[Hagar Delest]

Some explanation here: [Solved] Convert COUNTIFS from Excel to Calc.
Try:

Code: Select all

=IF(SUMPRODUCT($A$1:$A$12=I$1)>0;0;1)

Works with special characters too.

Please add [Solved] at the beginning of the title in your first post (top of the topic) with the 🖉 button if your issue has been fixed.

[MrProgrammer]

=IF(COUNTIF($A$1:$A$12;I1)>0;0;1)
Is there a way to differentiate between upper and lower case?

Not with COUNTIF.

COUNTIF not working

COUNTIF has several quirks which can make it difficult to use. I prefer SUMPRODUCT. See examples X25 and X26 in this tutorial.
[Tutorial] The SUMPRODUCT function

It also returns the incorrect answer when looking to verify that characters like <, >, = are in the range

That statement is meaningless because you don't tell why you think the COUNTIF result is wrong. Attach your spreadsheet so we can examine it. Telling us about the problem but not attaching the spreadsheet is like walking to your neighborhood mechanic and saying: Hey, I have this problem with my car at home. What do you think is wrong? Attaching a picture of the spreadsheet is like taking a picture of your car to the mechanic. I will not help further unless you attach the spreadsheet. Tell us what cell in what sheet has a value you don't expect. Tell us the expected value, and why that is the value you want.

[Geoff T]

Thanks to All
I'll look into your suggestions.

Cheers

[Geoff T]

Thanks for your input.

=SUMPRODUCT(EXACT($A$1:$A$12;I1))
solves the problem.

Cheers