Expense report - Grand total Calculation

  • Profile Image
    CPG_PACE
    Asked on June 21, 2021 at 11:13 AM

    I am trying to create an Expense Report form. I reviewed your templates and none of them seems to do the following for me:

    I need different areas (tables/grids) per category: Accommodation, Meals, Transportation....etc. - as per the attached screenshot.

    As per the screenshot, I have totals per category - i.e. Total HST for Meals, Grand total for Meals

    Up to here I managed by using the Spreadsheet fidget for each category and creating a formula at the last row to calculate the total HST and Grand Total.

    However I also need to have the Grand total of all totals calculated (Total Accommodation + Total Meals + Total Transportation), etc. In brief - the total of what the company needs to pay to the employee.

    I tried to use the "Form calculation" widget but I don't see how it can capture only certain cell within the spreadsheet (so I could make a calculation of all Grand Totals only).

    Could you please let me know how to achieve this?

    Thank you for your help!

    Screenshot
  • Profile Image
    ashwin_d
    Answered on June 21, 2021 at 02:42 PM

    I did check your form and found that you have added "Spreadsheet" widgets, and it is not possible to read the total of these widgets to calculate the grand total.

    I would suggest you to please add "Input table" field in form instead of using "Spreadsheet" widget and that will fit into your requirement. You can also use condition in form to perform calculation in input table. You may like to take a look at the following guide, which should help you: https://www.jotform.com/help/414-calculate-values-in-the-input-table-through-condition

    Hope this helps.

    Do try it out and get back to us if you have any questions.