jQuery script to sum values on rows and columns

Question

I have the following table structure:

<table width="960" border="1" align="center" id="activity_table">
<tr>
    <td width="192">Activity</td>
    <td width="192" align="center">Option 1</td>                        
    <td width="192" align="center">Option 2</td>
    <td width="192" align="center">Total</td>
</tr>
<tr>
    <td>Activity 1</td>
    <td align="center">
        <input type="text" name="activity_1_option_1" id="activity_1_option_1" value="0" class="col1" size="10" />
    </td>                        
    <td align="center">
        <input type="text" name="activity_1_option_2" id="activity_1_option_2" value="0" class="col2" size="10" />
    </td>
    <td align="center">
        <input type="text" name="activity_1_total" id="activity_1_total" value="0" class="" size="10" readonly />
    </td>
</tr>
<tr>
    <td>Activity 2</td>
    <td align="center">
        <input type="text" name="activity_2_option_1" id="activity_2_option_1" value="0" class="col1" size="10" />
    </td>                        
    <td align="center">
        <input type="text" name="activity_2_option_2" id="activity_2_option_2" value="0" class="col2" size="10" />
    </td>
    <td align="center">
        <input type="text" name="activity_2_total" id="activity_2_total" value="0" class="" size="10" readonly />
    </td>
</tr>
<tr>
    <td>Activity 3</td>
    <td align="center">
        <input type="text" name="activity_3_option_1" id="activity_3_option_1" value="0" class="col1" size="10" />
    </td>                        
    <td align="center">
        <input type="text" name="activity_3_option_2" id="activity_3_option_2" value="0" class="col2" size="10" />
    </td>
    <td align="center">
        <input type="text" name="activity_3_total" id="activity_3_total" value="0" class="" size="10" readonly />
    </td>
</tr>
<tr class="total">
    <td>Total</td>
    <td align="center">
        <input type="text" name="total_activity_1" id="total_activity_1" value="0" class="" size="10" readonly />
    </td>                        
    <td align="center">
        <input type="text" name="total_activity_2" id="total_activity_2" value="0" class="" size="10" readonly />
    </td>
    <td align="center">
        <input type="text" name="grand_total" id="grand_total" value="0" class="" size="10" readonly />
    </td>
</tr>

What I would like to do is to automatically compute the sum on each column and row.

I managed to write a jQuery script to compute the sum on each column, by adding colX (where X is the number of the column) as a class for the columns I want to sum, but I don't know how to do it in order to work for rows.

Here's my script:

$('#activity_table tr:not(.total) input:text').bind('keyup change', function() {
var $table = $(this).closest('table');
var total = 0;
var thisNumber = $(this).attr('class').match(/(\d+)/)[1];

$table.find('tr:not(.total) .col'+thisNumber).each(function() {
    total += +$(this).val();
});

thisNumber++;
$table.find('.total td:nth-child('+thisNumber+') input').val(total);
});

Do you know (in JavaScript context) how many activities and options you have beforehand? That would be pretty useful. — Daniel Böhmer
– Daniel Böhmer, Commented Jan 18, 2015 at 11:47
Yes, I know the exact number of options and activities. Actually the table is a lot bigger, I pasted just a sample for this question. — Cosmin
– Cosmin, Commented Jan 18, 2015 at 11:48

Rick Hitchcock · Accepted Answer · 2015-01-19 13:56:29Z

1

You can do this without referencing any classes, names, or ids within the table.

The code below gets the total for each column.

It works by grabbing the cellIndex of each td on row 2 (tr:eq(1)), which isn't the first or last column. It then filters all tds with a matching cellIndex on rows except the first row. If there's an input element, its value is added to a running total. Otherwise, we're on the last cell in the column, which gets the total value.

$('td:not(:first-child):not(:last-child)',
  '#activity_table tr:eq(1)').each(function() {
  var ci= this.cellIndex;
  var total = 0;
  $('td', 
    '#activity_table tr:gt(0)')
    .filter(function() {
      return this.cellIndex === ci;
    })
    .each(function() {
      var inp= $('input', this);
      if(inp.length) {
        if(!$(this).closest('tr').is(':last-child')) {
          total+= $('input', this).val()*1;
        }
        else {
          $('input', this).val(total);
        }
      }
    });
});

The code below gets the total for each row.

It iterates through each row, totaling its input elements.

$('#activity_table tr:gt(0)').each(function() {
  var total = 0;
  $('td:not(:first-child):not(:last-child)',
    this).each(function() {
    total+= $('input', this).val()*1;
  });
  $('input', this).last().val(total);
});

Note that on is now preferred over bind. If you remove input from the totals column and totals row, your event handler simplifies to this:

$('#activity_table input').on('keyup change',...

I recommend moving all styles to a stylesheet. I've done so in my Fiddle:

Fiddle

edited Jan 19, 2015 at 13:56

answered Jan 18, 2015 at 17:18

Rick Hitchcock

35.7k5 gold badges51 silver badges83 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Cosmin Over a year ago

Hello, Rick! Wonderful solution! Sadly, I have to use inputs as shown in my first post.

Cosmin Over a year ago

I'm sort of a jQuery newbie as you've probably already noticed from my code sample. Can you please update that fiddle in order to work with inputs for displaying totals? Thanks.

Cosmin Over a year ago

Thank you Rick! I know it works fine in your example, but when I moved the code in my app, I get NaN values in the inputs for the bottom totals. Do you know why? I haven't done any changes to your code, the only difference is that the real table has more rows and columns.

Rick Hitchcock Over a year ago

I see the problem. The row with "Pachet de raportări" doesn't have any input boxes. I've updated my answer and my fiddle.

Rick Hitchcock Over a year ago

Here's an update fiddle, without the last column, and with a button to insert new rows before the Totals row. It also changes the keyup change events to be delegated, so that the inputs can be created on the fly: jsfiddle.net/66h8jqdj

|

Daniel Böhmer · Accepted Answer · 2015-01-18 13:59:32Z

1

I adapted your HTML a bit. Having classes for all row/column total allows you to select them all at once. Avoid selecting elements with regular expressions as this will be really slow. If necessary you can use two classes like column column123 which allows you to select the group or a single item by class name. To access the indexes from the elements fast I use data- attributes. If elements are generated by jQuery code you can also use JavaScript data objects ("hashes") or jQuery's .data().

<html>
<head>
<script src="jquery.js"></script>
<script>
var rows = 3;
var cols = 2;

$( function() {
    var grand_total = 0;
    var row_totals = {};
    var col_totals = {};

    // I think there should be better approaches on initializing these
    for( var row = 1; row <= rows; row++ ) { row_totals[ row ] = 0 }
    for( var col = 1; col <= cols; col++ ) { col_totals[ col ] = 0 }

    $('input').each( function() {
        var $this = $(this);
        var val = $this.val() * 1; // the multiplication is to prevent string concatenation

        row_totals[ $this.attr('data-row') ] += val;
        col_totals[ $this.attr('data-col') ] += val;
        grand_total                          += val;
    } );

    $('.total.row').each( function() {
        var $this = $(this);
        $this.text( row_totals[ $this.attr('data-row') ] );
    } );

    $('.total.col').each( function() {
        var $this = $(this);
        $this.text( col_totals[ $this.attr('data-col') ] );
    } );

    $('.grand_total').text = grand_total;
} );
</script>
</head>
<body>

<table>
<tr>
<td class="total row" data-row="1"></td>
<td><input data-row="1" data-col="1" type="text" value="1">
<td><input data-row="1" data-col="2" type="text" value="2">
</tr>
<tr>
<td class="total row" data-row="2"></td>
<td><input data-row="2" data-col="1" type="text" value="3">
<td><input data-row="2" data-col="2" type="text" value="4">
</tr>
<tr>
<td class="total row" data-row="3"></td>
<td><input data-row="3" data-col="1" type="text" value="5">
<td><input data-row="3" data-col="2" type="text" value="6">
</tr>
<tr>
<td class="grand_total"></td>
<td class="total col" data-col="1"></td>
<td class="total col" data-col="2"></td>
</tr>
</table>

</body>
</html>

edited Jan 18, 2015 at 13:59

answered Jan 18, 2015 at 12:22

Daniel Böhmer

15.6k5 gold badges40 silver badges47 bronze badges

4 Comments

Cosmin Over a year ago

Thank you, Daniel, but I can't seem to make it work. I get no erros in the console, but also no totals. Later edit: seems that row_totals[ $this.attr('data-row') ] += +val; was supposed to be row_totals[ $this.attr('data-row') ] += val;. Also $this.text( row_totals[ $this.attr('data-row') ] ); must be $this.val( row_totals[ $this.attr('data-row') ] );.

Cosmin Over a year ago

After modifying your code as written above, the totals inputs got populated by values like "010101010", so I think the sum isn't computed correctly.

Cosmin Over a year ago

Oh, and also there are other tables in that page, so it's important to fit your jQuery code in an $('#activity_table input:text').bind('keyup change', function() {}); statement.

Daniel Böhmer Over a year ago

Yes, you'll have to adapt the code to your actual software. I just wanted to show a useful approach. The problem with +val is that JavaScript does hardly distinguish number and string variables. I used the plus to indicate a positive number. That works in Firefox. Which browser do you use? I'll edit with a real sum.

Collectives™ on Stack Overflow

jQuery script to sum values on rows and columns

2 Answers 2

Fiddle

8 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related