No counting space blanks

I have a list with number value results, if I leave any questions in blank
How I can make it that doesn’t count in my Total Results

Hi @Brian7, welcome to the AppSheet community.

You could make a virtual column called “Include” that uses a formula like this:

AND(ISNOTBLANK([Column1]), ISNOTBLANK([Column2]), ISNOTBLANK([Column3]))

Then, when doing your totals, only include those where [Include] = True. You could do that with a slice, potentially.

Hi Tony
Thanks for your help.
I have 9 tables with 51 columns each and with only 2 values allow( 0 and 1) . Therefore i need to use the formula for the 59 columns for each table and the Total will be [include] = true

Unfortunately you’d need to write an AND with 51 columns in it. And then repeat it 9 times, once per table.

Also, just a tip: if you find yourself having multiple tables with the same structure, it’s often easier to create a single table that combines the rows from all tables, with an extra column that designates which original table the row came from.

The tables are the same estructure but have different owners to audit.
You could be more explicit with the tip?
Sorry, I dont have a lot of experience

@Brian7
What @tony wants to explain with the tip is; provided all your tables are identical in structure, then you can create a Query Table which queries and concatenates all the data from the other table with an additional column to indicate from which table the data is queries from. Then you might not need to repeat that AND expression 9 times. A sample QUERY formula can be like this, collecting data from 3 tables:

=QUERY({Table1!A:D;Table2!A2:D;Table3!A2:D},"SELECT * where Col1 is not null ")

Thanks
I appreciated your help
I will try

Hi Tony

I am trying to uses the formula that you suggested me: AND(ISNOTBLANK([Column1]) and the app doesn’t accept it.

Thanks for your help

That’s not quite the same thing that I suggested. The AND function combines multiple conditions and evaluates to true when all of the conditions are true. It needs at least two conditions.

I got it

Thanks

1 Like

Hi Gentlemen,

How I can have a Total result like this: Yes / (Yes+ No)

You mean a numeric ratio of the results @Brian7?

This is the example
I have 3 questions to answer with Yes or No

  1. Yes
  2. No
    I would like obtain my result with only ( Every Yes answer divided by the Total questions), but If the question 2 was not answered I don’t want that question be part of the Total

Thanks,

@Brian7
And you want to make this calculation for each row?

@Brian7
If you want to make this calculation for each row:

IFS([Question1]=TRUE,1) + IFS([Question2]=TRUE,1) + IFS([Question3]=TRUE,1) / IFS(ISNOTBLANK([Question1]),1) + IFS(ISNOTBLANK([Question2]),1) + IFS(ISNOTBLANK([Question3]),1)
1 Like

I have a table with one question per column and a Total column

Hi Leventk,

Could I sum and divide columns?
I am interesting on sum and divide the results values of different columns and to obtain a Total column of that results

Thanks

@Brian7
Yes you can do that depending on what you want to achieve actually.