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

  • PeteSmithiesMedia
    Asked 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

    Jotform Thread 393660 Screenshot
  • jonathan
    Replied 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

    How can I have my matrix widget auto total values from rows and columns Image 1 Screenshot 30

     

    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

    How can I have my matrix widget auto total values from rows and columns Image 2 Screenshot 41

     

     

    Hope this help. Inform us if you need further assistance.

    Thanks!

     

     

  • PeteSmithiesMedia
    Replied 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

  • jonathan
    Replied 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

    How can I have my matrix widget auto total values from rows and columns Image 1 Screenshot 40

    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

    How can I have my matrix widget auto total values from rows and columns Image 2 Screenshot 51

     

    How can I have my matrix widget auto total values from rows and columns Image 3 Screenshot 62

     

    --

    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.

     

     

     

  • PeteSmithiesMedia
    Replied 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

  • Cesar
    Replied 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.

  • PeteSmithiesMedia
    Replied 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 does

    Surely there must be a solution?

    Pete

     

     

      

  • Cesar
    Replied 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 Screenshot 10

  • PeteSmithiesMedia
    Replied 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

  • Welvin Support Team Lead
    Replied 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

  • PeteSmithiesMedia
    Replied 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=41697542588874

    How can I have my matrix widget auto total values from rows and columns Image 1 Screenshot 20

     

    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

     

  • Welvin Support Team Lead
    Replied 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):

    How can I have my matrix widget auto total values from rows and columns Image 1 Screenshot 70

     

    While viewing it as Google Sheets, Click "File" and click "Share":

    How can I have my matrix widget auto total values from rows and columns Image 2 Screenshot 81

     

    Now, change the access permissions or share settings by clicking the "Change" link:

    How can I have my matrix widget auto total values from rows and columns Image 3 Screenshot 92

     

    Choose the Visibility Options and the Access

    How can I have my matrix widget auto total values from rows and columns Image 4 Screenshot 103

     

    Click Save and now get the Spreadsheet Link:

    How can I have my matrix widget auto total values from rows and columns Image 5 Screenshot 114

     

    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:

    How can I have my matrix widget auto total values from rows and columns Image 6 Screenshot 125

     

    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

  • PeteSmithiesMedia
    Replied 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 Lead
    Replied 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

  • PeteSmithiesMedia
    Replied on June 22, 2014 at 8: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

     

  • Ashwin JotForm Support
    Replied 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!

  • TitusN
    Replied 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. 

  • TitusN
    Replied 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.