COUNT AND LIST NAME FOR FEE TABLE

AppSheet AppSheet Q&A
, ,
1

1C2500AB-A170-401A-8432-7A9CCDCAF8B1.jpeg
1C2500AB-A170-401A-8432-7A9CCDCAF8B1.jpeg2362×509 113 KB

  1. I want to count the number of months that have not been ticked in the Appsheet.

  2. I want to create a list of names that have not been ticked for each month in the picture above in the Appsheet formula.

help me

2
  1. Count the number of months that have not been ticked:

Assuming you have a column for each month (e.g., [January], [February], etc.) with checkboxes (TRUE/FALSE), you can use the following AppSheet formula to count the number of months that are unchecked:

COUNT(

LIST([January], [February], [March], [April], [May], [June], [July], [August], [September], [October], [November], [December])

  • LIST(TRUE)

)

This formula creates a list of all month columns, removes the checked ones (TRUE values), and counts the remaining unchecked months.

  1. Create a list of names that have not been ticked for each month:

Assuming you have a table where each row represents a person and the columns represent the months they have checked (TRUE/FALSE), you can create a virtual column [Unchecked_Months] with this expression:

LIST(

IF(NOT([January]), “January”, “”),

IF(NOT([February]), “February”, “”),

IF(NOT([March]), “March”, “”),

IF(NOT([April]), “April”, “”),

IF(NOT([May]), “May”, “”),

IF(NOT([June]), “June”, “”),

IF(NOT([July]), “July”, “”),

IF(NOT([August]), “August”, “”),

IF(NOT([September]), “September”, “”),

IF(NOT([October]), “October”, “”),

IF(NOT([November]), “November”, “”),

IF(NOT([December]), “December”, “”)

) - LIST(“”)

This formula creates a list of months that are unchecked for each person.

To get all names that have at least one unchecked month:

SELECT(YourTable[Name], COUNT([Unchecked_Months]) > 0)

To get names specifically for a given month, say March:

SELECT(YourTable[Name], NOT([March]))

Let me know if you need adjustments based on your actual table structure!

3

How do I create a formula in appsheet using a virtual column, a list of students by month that is unchecked? The data is arranged in table form.

4
  1. Your table should have:

• A [Student Name] column.

• A [January], [February], …, [December] column (each storing TRUE/FALSE values for checkmarks).

  1. Virtual Column for Each Month

Create a virtual column, e.g., [Unchecked_Students_January], in the Months Table (or wherever appropriate). Use this expression:

SELECT(Students[Student_Name], NOT([January]))

This will return a list of students who have not checked January.

  1. Create for Each Month

Duplicate the above virtual column for other months:

SELECT(Students[Student_Name], NOT([February]))

SELECT(Students[Student_Name], NOT([March]))

…

SELECT(Students[Student_Name], NOT([December]))

  1. Create a Single Virtual Column for All Unchecked Months

If you want a single column listing each student and their unchecked months:

LIST(

IF(NOT([January]), “January”, “”),

IF(NOT([February]), “February”, “”),

IF(NOT([March]), “March”, “”),

IF(NOT([April]), “April”, “”),

IF(NOT([May]), “May”, “”),

IF(NOT([June]), “June”, “”),

IF(NOT([July]), “July”, “”),

IF(NOT([August]), “August”, “”),

IF(NOT([September]), “September”, “”),

IF(NOT([October]), “October”, “”),

IF(NOT([November]), “November”, “”),

IF(NOT([December]), “December”, “”)

) - LIST(“”)

This virtual column will return a list of unchecked months per student.

  1. Group Students by Unchecked Month

If you have a Months Table and want to display all students per month:

  1. Add a virtual column in the Months Table (e.g., [Unchecked_Students]).

  2. Use this formula:

SELECT(Students[Student_Name], NOT(IN([_THISROW].[Month], Students[Checked_Months])))

This will return all students who have not checked the current month in the Months Table.