-
PeteSmithiesMediaAsked on June 19, 2014 at 2:37 AM
I would like to have my form automatically calculate row and column totals. Is this possible in Jotform?
Regards
Pete -
jonathanReplied on June 19, 2014 at 4:18 AM
Hi Pete,
Unfortunately you cannot do this on the regular Matrix Table field.
What I suggeset you used instead is the Spreadsheet Widget
Using the Spreadsheet widget will allow you to create column formula that can be calculated to total.
Here is the demo form using Spreadsheet widget http://www.myjotform.com/form/40983218494564
Hope this help. Inform us if you need further assistance.
Thanks!
-
PeteSmithiesMediaReplied on June 19, 2014 at 5:47 PM
Thanks for that, However, I am not sure how I enter the information in the Total (D) cell to calculate how many of the cells have entries.
e.g In your example above the Total for Row 2 would be 3
Also - I need to change A, B, C, D etc to read: Description, B#, B#.... and Total.
How can I do this?
Or better yet, as I can build exactly what I want in Excel, can I do that and simply find a way to "insert into the jotform"???
Regards
Pete -
jonathanReplied on June 19, 2014 at 6:11 PM
Hi Pete,
Actually, when you click on the cell (Total (D)) , you will be able to see the formula used
To have a different column header, you can use the optional CSS code to hide the default headers (A,B,etc) and then just add your own default text on the first row that will act as the column header
--
Or better yet, as I can build exactly what I want in Excel, can I do that and simply find a way to "insert into the jotform"???
To achieve this, you will need to share your Excel file on a website before you can embed it within the form.
You can do similarly using Google spreadsheet instead. But just the same, you will have to share the link before you can allow interaction on the embedded spreadsheet.
Please inform us if you need further assistance.
Thanks.
-
PeteSmithiesMediaReplied on June 19, 2014 at 6:39 PM
Sorry, I am not quite getting this, If I integrate - it seems to just populate the results from the jot form into the google spreadsheet. What I want to do is embed a new spreadsheet created in Google into my JOTFORM to achieve what I need?
I had another look at the spreadsheet widget - and whilst your totals worked--- what I actually need is for the totals column to just cound the number of cells in each row that are populated:i.e in Excel the formula for this would be something like: =COUNT(B2:I2)
If I could get that to work.. the only other thing I would need is the TOTALS column to be added together, but cannot see any way of doing this in JotForm Spreadsheet Widget
Am I missing something?
Pete -
CesarReplied on June 19, 2014 at 7:11 PM
You can showcase the Google spreadsheet on your form or on any website by publishing the the Spreadsheet, using this guide: Embed a spreadsheet in a blog or webpage
This is my example form: http://form.jotformpro.com/form/41697057825970?
I have embedded the spreadsheet back to the for using the Text tool and the iframe code provided by Google.
You can clone my form for further testing.
Do let us know if you need further assistance. Thank you.
-
PeteSmithiesMediaReplied on June 19, 2014 at 8:26 PM
I did a test, trying to embed the Google Spreadsheet but I dont like the results - I get a thumbnail preview of the spreadsheet that I dont seem to be able to get rid of.
So I have looked again at the spreadsheet widget - but there appears to be issues with that too...It does not seem to update as you enter values into the totals end column, nor will it accept the count code.
All I really want to be able to do is embed a simple spreadsheet that does exactly what this doesSurely there must be a solution?
Pete -
CesarReplied on June 19, 2014 at 10:47 PM
I have tested the Spreadsheet widget, but it appears it does not allow vertical sums. I will escalate this to the Development Team so that they can review the feasibility of implementing a vertical sum feature to the spreadsheet widget. Unfortunately I'm not able to promise if this is possible or not. Nor can I provide an ETA for when this ticket will be processed, Nevertheless, we will keep you posted once we have any updates from that team.
Spreadsheet Widet is Unable to Perform Vertical Sums at the moment:
-
PeteSmithiesMediaReplied on June 22, 2014 at 12:47 AM
Thanks for that, however, Can you look at my last post and respond to that?
Thanks
PeteI did a test, trying to embed the Google Spreadsheet but I dont like the results - I get a thumbnail preview of the spreadsheet that I dont seem to be able to get rid of.
So I have looked again at the spreadsheet widget - but there appears to be issues with that too...It does not seem to update as you enter values into the totals end column, nor will it accept the count code.
All I really want to be able to do is embed a simple spreadsheet that does exactly what this doesSurely there must be a solution?
Pete -
Welvin Support Team LeadReplied on June 22, 2014 at 4:34 AM
Hi Pete,
What thumbnail? Maybe, you forget to include the spreadsheet sizing? How about the way you embed the Google Spreadsheet?
Here's an example form that shows the embedded spreadsheet: http://www.jotformpro.com/form/41721487338964
With regards to the Spreadsheet Widget, my colleague have reported the issue with regards to the ability of doing vertical sum. We'll you know if there are updates on that.
Thanks
-
PeteSmithiesMediaReplied on June 22, 2014 at 5:31 PM
Hi again,
Here is the link to a clone of the form with the embedded Google spreadsheet:
http://www.jotform.com//?formID=41697542588874There are 2 issues...
1. It shows a thumbnail on the left (I dont want that) and
2. You cannot enter values into it!!
Regards
Pete -
Welvin Support Team LeadReplied on June 22, 2014 at 6:49 PM
Hi Pete,
My apologies! I think I should have explain the way you should embed the spreadsheet. First thing, you have to view the spreadsheet using Google Sheets (not the web viewer):
While viewing it as Google Sheets, Click "File" and click "Share":
Now, change the access permissions or share settings by clicking the "Change" link:
Choose the Visibility Options and the Access
Click Save and now get the Spreadsheet Link:
Go back to your form in edit mode, look for the iFrame Embed widget, add it to your form, paste the spreadsheet link to the URL section, change the width and height, as specified to this screenshot:
Save you form and that's it! You should be able to view the spreadsheet, editable and without the thumbnail.
Let us know if you have any questions.
Thanks
-
PeteSmithiesMediaReplied on June 22, 2014 at 7:39 PM
Thank you again for that, I now have the form looking like I want it
The only problem now is...
The information that is sent via email from the form contains none of the data that is entered into the iframe embedded Google spreadsheet??
I need this information to be included in the sent email.
Regards
Pete -
Welvin Support Team LeadReplied on June 22, 2014 at 7:56 PM
Hi Pete,
Again, please accept my sincere apologies. At the first place, it's obvious that you want to get the data from the sheet.
Embedding the spreadsheet and allowing someone to modify the cells doesn't give you the ability to receive the unique data that was filled by the submitter. Meaning, anyone who has the access to the spreadsheet would be able to modify the entries, overriding the first one.
So now, I think embedding the spreadsheet isn't the solution or couldn't be the same as the Spreadsheet Widget. For now, what we can do is to wait for our developers to give us an update with regards to the ability to do vertical sum.
Again, I'm sorry!
Thanks
-
PeteSmithiesMediaReplied on June 22, 2014 at 8:06 PM
-
Ashwin JotForm SupportReplied on June 22, 2014 at 10:10 PM
Hello Pete,
On behalf of my colleague, you are welcome.
Since we cannot create multiple ticket in one thread. I have moved your feature request related to spreadsheet widget in a new thread. You will be answered in the following thread: http://www.jotform.com/answers/394699
Thank you!
-
TitusNReplied on July 5, 2014 at 8:38 AM
Hello,
We have implemented the count() function.
You can implement it by either:
count ( a1:a17 ) or count ( a1,a2,a3,a4 )
Here is a sample form based on your request: http://form.myjotform.com/form/41842019099559?
You may clone the form and take a closer look.
Your feedback is greatly appreciated.
-
TitusNReplied on July 5, 2014 at 8:46 AM
I forgot to mention that the vertical summation has been implemented on the spreadsheet widget as well.
You can use cell ranges with the sum() function
For Instance:
j18 = sum ( j2:j17)
Let us know how it works out.