Apache OpenOffice Community Forum

=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. 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

ctrl ⇑ enter

Some explanation here: [Solved] Convert COUNTIFS from Excel to Calc.
Try: 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.

Geoff T wrote: ↑Mon Jul 22, 2024 4:11 pm =IF(COUNTIF($A$1:$A$12;I1)>0;0;1)
Is there a way to differentiate between upper and lower case?

Not with COUNTIF.

Geoff T wrote: ↑Mon Jul 22, 2024 4:11 pm 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

Geoff T wrote: ↑Mon Jul 22, 2024 4:11 pm 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.

Thanks to All
I'll look into your suggestions.

Cheers

Thanks for your input.

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

Cheers