Sum 02)

02) Sum Data Based on Your Criteria - Cross Column Format

This function allows you to select criteria and summation columns and creates a new workbook adding up the values in the summation columns based on the criteria columns.

To Use:

Using the  header row click a single cell representing  your first criteria column, then holding the ctrl-key select other criteria cells in that same header row. (the last criteria column you select will become the Header Row in your Cross Tab display). Still holding the ctrl-key, and using a different row, select single cells representing the columns you wish to sum.

Now let up on the ctrl-key and navigate to this option which is found under the SUM menu item on The Data Wand toolbar.


You may not have a header row which identifies the data below. In this case you’ll still use a higher row to select your criteria columns and a lower row to select your summation columns. When you are prompted “Did you start with a header row” answer “No” to let the routine know it should treat that row as data.

Since the last criteria column you select will become the Header Row on your Cross Tab result you will have the opportunity to choose Left to Right or Right to Left. Choosing the default “R” will sort ascending (left to right).

Other Considerations:

This function works best on contiguous rows of data. It will read every row until it reaches a blank in the column of the first criteria column selected.

To make it skip those blanks and continue reading the rows then your first criteria selection can be the entire column, or it can be a set number of rows. By selecting a range of rows you are telling the function to read those selected rows, whether or not there are blank rows in between.

Queries | Lists | Find | Alter | Clean | Sum | Functions | Help | Misc