What is JotForm?
JotForm is a free online form builder which helps you create online forms without writing a single line of code. No sign-up required.

At JotForm, we want to make sure that you’re getting the online form builder help that you need. Our friendly customer support team is available 24/7.

We believe that if one user has a question, there could be more users who may have the same question. This is why many of our support forum threads are public and available to be searched and viewed. If you’d like help immediately, feel free to search for a similar question, or submit your question or concern.


  • Profile Image

    How can I have my matrix widget auto total values from rows and columns

    Asked by PeteSmithiesMedia on June 19, 2014 at 02:37 AM

    I would like to have my form automatically calculate row and column totals. Is this possible in Jotform?
    Regards


    Pete

    Page URL:
    http://www.kellswool.co.nz/online-forms.html

    Screenshot
    rows and columns rows calculate column spreadsheet widget total spreadsheet matrix total
  • Profile Image
    JotForm Support

    Answered by jonathan on June 19, 2014 at 04: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!

     

     

  • Profile Image

    Answered by PeteSmithiesMedia on June 19, 2014 at 05: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

  • Profile Image
    JotForm Support

    Answered by jonathan on June 19, 2014 at 06: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.

     

     

     

  • Profile Image

    Answered by PeteSmithiesMedia on June 19, 2014 at 06: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

  • Profile Image

    Answered by Cesar on June 19, 2014 at 07: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.

  • Profile Image

    Answered by PeteSmithiesMedia on June 19, 2014 at 08: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 does

    Surely there must be a solution?

    Pete

     

     

      

  • Profile Image

    Answered by Cesar 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:

    https://shots.jotform.com/cesar/screens/June14/verticalsum.png

  • Profile Image

    Answered by PeteSmithiesMedia on June 22, 2014 at 12:47 AM

    Thanks for that, however, Can you look at my last post and respond to that?
    Thanks

    Pete

     

    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 does

    Surely there must be a solution?

    Pete

  • Profile Image
    JotForm Support

    Answered by Welvin on June 22, 2014 at 04: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

  • Profile Image

    Answered by PeteSmithiesMedia on June 22, 2014 at 05:31 PM

    Hi again,

    Here is the link to a clone of the form with the embedded Google spreadsheet:

    http://www.jotform.com//?formID=41697542588874

     

    There 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

     

  • Profile Image
    JotForm Support

    Answered by Welvin on June 22, 2014 at 06: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

  • Profile Image

    Answered by PeteSmithiesMedia on June 22, 2014 at 07: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 

     

  • Profile Image
    JotForm Support

    Answered by Welvin on June 22, 2014 at 07: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

  • Profile Image

    Answered by PeteSmithiesMedia on June 22, 2014 at 08:06 PM

    HI Welvin,


    Thanks for that.

    Will you also please ask if they can add the facility to "count" how many cells have data entered too.

    SO I can have a row of cells where for example 5 cells have entries of differing amount but the end row "TOTAL" cell would count and display "5"

    Thanks


    Pete

     

  • Profile Image
    JotForm Support

    Answered by ashwin_d 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!

  • Profile Image
    JotForm Support

    Answered by TitusN on July 05, 2014 at 08: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. 

  • Profile Image
    JotForm Support

    Answered by TitusN on July 05, 2014 at 08: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.