Count of the letter P

AppSheet AppSheet Q&A
1

I have a small school that has a weird attendance policy by period. The policy is that if they attend 3 periods or more then they are considered present. This is an example

Student ID Date Per1 Per2 Per3 Per4 Per5 Per6
123456 8/10/2022 P P P P P P
123456 8/11/2022 P P P P P P
123456 8/12/2022 A A A A P P

I would like a formula that is something like for the day if they have > 3 “P” then give them a “Y” in a virtual column for present. That way I can add up all the days to give them the correct attendance. Any ideas?

2

Can you control the schema of that table? I ask because it’s a little bit against the normalization rules. Not an AppSheet thing per se, but it could help a lot

Data management - AppSheet Help

Now, if you really want to do it with your current setup:

COUNT(
 LIST(
  [Per1],
  [Per2],
  [Per3],
  [Per4],
  [Per5],
  [Per6]
 )-
 LIST("A")
)

This should give you the amount of “P” from those columns. You could change that to remove P so you count A and so on…

1 Like
3

That worked except I have weird results. There is one person

Per1 Per2 Per3 Per4 Per5 Per6 Present
123456 8/12/2022 T P P P P P 1

The count of P for that day should be 5 and not one. Is it because im not counting the amount for that day?

4

That’s a weird result, although now that I see you can have more than just “P” and “A”, you could change the expression a bit:

6-
COUNT(
 LIST(
  [Per1],
  [Per2],
  [Per3],
  [Per4],
  [Per5],
  [Per6]
 )-
 LIST("P")
)
1 Like
5

Only one I am concerned about is the P in this case. They way it is structured if they miss it doesn’t matter but a P matters for reporting

6

Remember, list subtraction has the side-effect of removing duplicates.

7

Man, you are right, quite obvious.

This cumbersome one then:

SUM(
 LIST(
  IFS([Per1]="P", 1),
  IFS([Per2]="P", 1),
  IFS([Per3]="P", 1),
  IFS([Per4]="P", 1),
  IFS([Per5]="P", 1),
  IFS([Per6]="P", 1)
 )
)
1 Like
8

One step further if I can ask, I made a new column that if the count is >= 3 then give a yes value, I now need a count of all the yes values a student has. Something like this

COUNT(
Select(
[Day Present][_ROWNUMBER],
([Day Present] = “Yes”)
)
)

That code does not work though