Newbie Formula Help for Count

1

date 1 date 2 date 3 date 4 Plays
P P P,b
How do I write a formula to indicate the number of times P appears in all rows?

2

I did a terrible job with this post. I copy/pasted a visual of what I meant, but when posted, it all collapsed into a mess. So let me better explain: The left column is a list of tennis players. The columns across the top are dates of play. In the cells next to each player I put P if they are playing on that date, P,b if they are playing & must bring balls, O if this is an Out date for them or blank if they are not playing. I have a final column called Plays. I would like that column to calculate then number of Ps in that player’s cells, so we can see how many times each player is playing. Thanks for any help anyone might provide.

3

Hi @Albert_Yoder :blush: !

Thank you very much for the clarifications :grin: !

You could use in your field Plays a formula looking something like this :

List(
  thisRow.[Date 1],
  thisRow.[Date 2],
  thisRow.[Date 3],
  thisRow.[Date 4]
).Filter(CurrentValue.Lower().Find(Lower("P")) != -1).Count()

This will count all the P in your diverse “Dates” fields (the one with the values P,b included)

What is does is :

Create a list of the values in your “Dates” fields and then filter those values (CurrentValue), once lowered (Lower()) where Find(Lower("P")) != -1 (i.e.: where the result of Find(Lower("P")) is not equal -1. If it returns -1 it would means there’s no P's) and then, it counts those results :blush:

A quick sample, to illustrate :blush:

You’ll find more info about Find() here :point_down: :blush:

There are other possibilities (using a mix of FormulaMap() & RegexMatch(), for example) if this one doesn’t work for you :blush: …

Hope this helps :blush:

1 Like
4

Brilliant! I would never have worked that out, but now that I see it I understand the logic. Many thanks.

5

No problem :grin: !
Always glad to help when I can :blush: !