Question

Sum of table values is zero if first row is 0

Version 11 Forms Self-Hosted Forms Layout Designer

Updated one day ago

asked two days ago

I have a form that contains more than one table that has items in each table populated by a lookup. The user of the form adds a 'score' on each item of each table, and there is a field at the bottom of the form that calculates the total score by what the user enters for scores on each row of the tables. They can either enter a 0 or 1, which is dictated by the number field configuration in the form in each table. If they enter a 0 in the first row of the table, the entire table is not included in the total score.

Here is an example when the first rows of each table has a '1' (only showing the first 2 tables for simplicity sake):

Here is the total at the bottom of the form, which is correct:

When I change the first score of one of the tables to '0', it doesn't count any of the rows in the calculation:

Here is the total at the bottom, which is incorrect:

When I change the row back to have a '1', the total is correct again.

Here is the calculation that I have on the 'Total' field:

=IF(Pump_Area.Item_Score<>"",SUM(Pump_Area.Item_Score),0)+
IF(Curb_Appeal.Item_Score_1<>"",SUM(Curb_Appeal.Item_Score_1),0)+
IF(Building.Item_Score_2<>"",SUM(Building.Item_Score_2),0)+
IF(Food_Area.Item_Score_3<>"",SUM(Food_Area.Item_Score_3),0)+
IF(Sales_Floor.Item_Score_4<>"",SUM(Sales_Floor.Item_Score_4),0)

I am using the Modern designer in Forms 11 self-hosted, version 11.0.2311.50553

0 0

Answer

SELECTED ANSWER

replied two days ago

In a Modern-Designer expression, the moment you compare a collection ( Pump_Area.Item_Score is an array that holds every score in that column) with something else, Forms collapses the array to the first element before it evaluates the comparison.

IF( Pump_Area.Item_Score <> "", … )

When the first score is 1 the comparison is 1 <> "" → TRUE, so SUM() runs and the whole table is included.

When the first score is 0 the comparison is 0 <> "" → FALSE (zero is treated as “empty” in this context), so you fall into the else branch and the table contributes 0 to the total—no matter what the other rows contain.
You can avoid touching the first element altogether and just look at the table as a whole:

=IF(
     COUNT(Pump_Area.Item_Score)>0 ,
     SUM(Pump_Area.Item_Score) ,
     0
 )
+ IF(
     COUNT(Curb_Appeal.Item_Score_1)>0 ,
     SUM(Curb_Appeal.Item_Score_1) ,
     0
 )
+ IF(
     COUNT(Building.Item_Score_2)>0 ,
     SUM(Building.Item_Score_2) ,
     0
 )
+ IF(
     COUNT(Food_Area.Item_Score_3)>0 ,
     SUM(Food_Area.Item_Score_3) ,
     0
 )
+ IF(
     COUNT(Sales_Floor.Item_Score_4)>0 ,
     SUM(Sales_Floor.Item_Score_4) ,
     0
 )

3 0

View 1 previous reply

replied one day ago

I'm glad it was helpful. Thank you

1 0

replied one day ago

I also wanted to mentioned that the explanation was helpful as well in understanding why I was getting the results that I was. Thanks again.

1 0

replied one day ago

My thanks go out to you for bringing this question or problem to the forum. Thanks to it, I began to investigate the causes, and by reading and consulting parts of the documentation, I was able to find a summary of the information I shared. So the learning process was mutual. Thank you for your kind words

0 0

Replies

replied two days ago

Apologies, but I'm a little confused, why are you using an IF statement rather than just a SUM?

I'm probably missing something and oversimplifying it, but if you're just wanting the grand total of 1's and 0's in all of your tables, then something like this should work:

=SUM(Pump_Area.Item_Score,Curb_Appeal.Item_Score_1,Building.Item_Score_2,Food_Area.Item_Score_3,Sales_Floor.Item_Score_4)

5 0

replied one day ago

Ah, so simple... that worked! I was trying to avoid circumstances where the table might be empty, but so far I don't have a situation where the table is empty, so I will apply this solution as long as it works. Thank you!

1 0

replied one day ago

Happy to help! If the table is empty then the total value should be read as 0, I believe, which is essentially what your IF statement was saying anyway. :)

0 0

replied two days ago • Show version history

Try wrapping your SUMIFs in an IF statement that looks at first row only.
Something that is more like IF(INDEX(Pump_Area.Item_Score,1)=1,SUMIFFS,0)

1 0

replied two days ago

If I understand correctly, you've detailed what the current behavior is, but not stated what the expected behavior is. Are you attempting to sum all of the tables? If so, your "TOTAL" formula may be incorrect because you are using "+" to try to sum instead of the SUM() function. Change it to:

=SUM(IF(Pump_Area.Item_Score<>"",SUM(Pump_Area.Item_Score),0),0)
IF(Curb_Appeal.Item_Score_1<>"",SUM(Curb_Appeal.Item_Score_1),0),
IF(Building.Item_Score_2<>"",SUM(Building.Item_Score_2),0),
IF(Food_Area.Item_Score_3<>"",SUM(Food_Area.Item_Score_3),0),
IF(Sales_Floor.Item_Score_4<>"",SUM(Sales_Floor.Item_Score_4),0))

0 0

replied two days ago

Yes, I am trying to get a total score from all the tables combined. I did try your suggestion, but got the same result. I also tried the following, with the same result:

SUM(SUMIF(Pump_Area.Item_Score,<>""),
SUMIF(Curb_Appeal.Item_Score_1,<>""),
SUMIF(Building.Item_Score_2,<>""),
SUMIF(Food_Area.Item_Score_3,<>""),
SUMIF(Sales_Floor.Item_Score_4,<>""))

0 0

You are not allowed to follow up in this post.

Question

Question

Sum of table values is zero if first row is 0

Answer

Replies

Sign in to reply to this post.