COUNTIF in column of RIGHT string values

[Dobermann]

I have a column of characters (that look like numbers but are really text), such as:

13-09-07-01

13-09-99-99

13-06-07-01

13-07-07-01

16-09-07-01

16-09-99-99

19-12-07-01

19-12-99-99

19-02-07-04

19-02-99-99

19-05-07-01

I need to count the number of rows whose value ends in “99-99”. I just cannot get it to work. What am I doing wrong?

=COUNTIF(B2:B316;RIGHT(5)=“99-99”) returns 0

=COUNTIF(RIGHT(B2:B316;6)="-99-99") returns Err:511

=COUNTIF(B2:B316;RIGHT(5)"=99-99") returns Err:509

=COUNTIF(B2:B316;“RIGHT(6)=-99-99”) returns 0

=SUMPRODUCT(B2:B316="-99-99") returns 0

I’ve tried moving the quotes around so many times that my eyes are crossing. I give up. I’d appreciate any help, thanks!

[Dobermann]

Yes! Thank you @anon73440385!! I NEVER knew about CTRL+SHIFT+ENTER before your post. So that applies { } to the formula, turning it into a function? And it needs to be turned into a function because RIGHT() does not work on ranges?

The Help function does not tell you that RIGHT() does not work on ranges. I wonder how many more commands do not and how people learn them. But I really wonder why it works as a function and not normally. Boy, do I have a LOT to learn!

Who would have thunk! Well, obviously, you did!

Thank you ever so much!!

[LeroyG]

@Dobermann, Just over this comment is a “more” button. Please, touch it and select “repost as a question comment”. Thanks.

[anon73440385]

So that applies { } to the formula, turning it into a function?

No - it turns it to an array formula, which means that LibreOffice uses the function RIGHT() for each element specified in the range and puts the result into an array.

> The Help function does not tell you that RIGHT() does not work on ranges.

That’s not correct. Help to function RIGHT() clearly states:

RIGHT("Text" [; Number]) 
Text is the text of which the right part is to be determined.
Number (optional) is the number of characters from the right part of the text.

and a range is not “Text”. Only a reference returning “Text” does work.

[Dobermann]

@LeroyG My apologies. Still learning. I fixed it and will remember the correct way in the future. Thanks.

[Dobermann]

@anon73440385 Oh, interesting about it putting the result into an array. I feel like I am lucky if I know 1% of LibreOffice’s power unlike someone like you who obviously knows tons and tons more.

For someone who obviously really does not know all about the program, when I read that, I never made the connection between a range not being text. To my way of thinking, a range still had text in it, therefore, it was text. Someone like me would have made the connection if it was in more of layman terms, such as something like what you said, “A range is not “Text”.”

If Help had said that, it would have made it so much clearer to me. I’m sure it’s not so to you as someone who knows a lot about the program, but to someone like me, not much high level knowledge of it, I’m still learning it and need a more in-your-face obvious explanation, not an explanation by inference. Granted, at the time, I still would not have known about turning it into an array formula.

Again, thanks for your help.

[anon73440385]

Hello,

use

=SUMPRODUCT(COUNTIF(RIGHT(A1:A11;5);"99-99")) --or-- array formula
{=COUNTIF(RIGHT(A1:A11;5);"99-99")} (remember CTRL+SHIFT+ENTER to enter such formula)
(you need to apply the function to data and compare the result; SUMPRODUCT force array evaluation, otherwsie RIGHT() won’t work as intended since it doesn’t support ranges)

Hope that helps.