Result of sum function doesn't match data

In one of my forms, I have three columns A, B, and C, where A + B = C (counting the number of student, non-students, and total participants respectively). On each line, A + B = C, but when I sum the columns, A and B are less than I would expect, meaning sum(A) + sum(B) != sum(C).

I exported the data into Excel and summed them there, and the sum values were correct. This would mean that there isn't an issue with the data, but with how Jotform is summing it. In the attached screenshot, the top numbers are the Excel sums, and the bottom numbers are the column sums from Jotform.

Thank you.

Hi CUTC_Technowizard, 

Thanks for reaching out to Jotform Support. Sure, I can look into this for you. I’ll just need access to your account to do that. Don't worry, you can disable that again after we're done checking things out. Let me show you how to do it:

1. In the upper-right corner of your My Forms page, click on your Avatar/Profile Image icon.

2. In the small window that opens, click on Settings.

3. In the panel on the left, click on the Security tab.

4. Then, on the bottom-right side of the page, toggle Jotform Support Access to the On position.

After we hear back from you, we’ll have a better idea of what’s going on and how to help.

Hi Aries,

I've enabled that setting.

Thanks,
CUTC

Hi CUTC,

I’ll need a bit of time to look into this. I’ll get back to you as soon as I can.

Thanks for your patience, we appreciate it.

Hi CUTC,

I made a couple of tests, I experience a different issue. In my case, the value is not summing up. Can you share if you've manually added column C in your Microsoft Excel?

Once we hear back from you, we'll be able to help you with this.

Hi Chris,

In Excel, the only changes I made to the exported set of data was to add the sum cells at the bottom that are shown in the image.

Hi CUTC,

I checked your submission table and saw the discrepancy of the sum of the 3 columns when I totaled each column in Microsoft Excel. After cloning your form and importing the data of your submissions in another tab in Jotform Tables, I saw that the column format of these columns was text instead of number. There's an easy fix, let me show you how:

Download your data from Jotform Tables as Excel:

1. On your My Tables page, hover over the form whose data you want to download, and click on View Table.
2. On the upper-right side of the page, click the on Download All button. 
3. Now, select Download as Excel.

Creating a New Tab for the Submissions:

1. In Jotform Tables, click on New Tab at the top of the page next to your tabs.

2. In the menu that opens, select Table and click on Next.

3. Now, select Import Data, and click on Next again.

4. Select the same form, and click on Next.

5. Then, click on the Column Types next to Imported Columns, and choose Number from the options, and select the Create button.

That's it. Give it a try and let us know if you have any other questions.

Hi Kyle,

The form is still active and taking submissions, so I need the table linked to the form itself, not just a copy of the data. It seems the option to select column types is only available when importing from a file, and not from a form, so this solution won't work for us.

The Total Students and Total Nonstudents columns are from an Advanced Formula field using a sum function, and the Number of Team Members column is from a Number field. I would expect all three of them to be number types to start off with (compared to something like getting a user to type a number into a short-text field).

CUTC

Hi CUTC,

I’ll need a bit of time to look into this. I’ll get back to you as soon as I can.

Thanks for your patience, we appreciate it.

Hi CUTC,

Since the option to change column types is only available during data import and not directly on live form tables, this limitation can cause the sums to appear incorrect within Jotform Tables even though the data itself is accurate, as confirmed by your Excel export. To address this, one approach is to ensure that the fields involved in calculations are explicitly set as Number fields or use calculation widgets that output numeric values. You might want to double-check that the formula fields are configured to return numeric results rather than text strings. Currently, Jotform Tables does not support changing the column data type for live form data directly. A workaround is to export the data and perform calculations externally, as you have done with Excel. If you want to maintain live calculations within your form, consider redesigning the form fields to use number fields and calculation widgets that guarantee numeric outputs, which should then sum correctly in Jotform Tables.

Give it a try and let us know if you need any help.