Everything You Need To Know About Adding Totals to the Final Row of Your Dataset

[Ozzie]

Here in Customer Support, we often get asked from new users how they can add total sums for every column onto the bottom of their datasets. There are several ways to do this.

An easy suggestion is using the Summarize and Union tools to sum the rows and add those sums back into the bottom of that dataset. Sounds simple right?

An even easier suggestion is where you show clients the lovely “Add Totals” macro that is available from the CReW Macro pack, which works for any sized dataset.

However, there is one limitation to this macro and that is its ability to add a word like “Total” or ”Grand Total” to the string field right before the sums of your rows. This is obviously useful for those making reports and tables in Excel.

Attached to this article is a workflow I created (version 10.1) that shows the functionality of this macro as well as how to add the word “Totals” before the summations. Also you can see how I created row totals with and without using the CReW macro. Below I’ll explain how I go about adding this row if you are using the CReW macro. I suggest you go ahead and download the attached workflow to explore how to do this without using the macro.

First, place a RecordID tool before you use the “Add Totals” macro. Add a sample tool in one stream out of the macro to get the last record.

Use a Multi-Field Formula tool to replace all Null values in string fields with the word “Total”. In a separate stream, add a filter tool and filter out where all instances of RecordID are Null since Add Totals macro won’t sum this stream up if you unchecked it in the macro configuration.

Lastly remove the RecordID’s using a select tool and Union the data back together using the default option: “Auto Config by Name”.

This should work for datasets of all sizes, therefore this is a useful trick to use when creating a macros that will be used for outputting report tables.

[HLCruz13]

Extremely helpful!!! Thank you so much!

[bhaviksodha]

Thank you Ozzie, I used your workflow and it was very helpful !!!!!

Thanks, 

Bhavik

[adam_nyc]

Hello. I am trying to add a TOTAL row underneath where the column data is a combination of sum and weighted averages.  Is there a good way to accomplish this?

Thanks
Adam