Excel Tool

Order Online
Our company contact information There you can find frequently asked questions and answers

Home | Products |Purchase | FAQ | Contact Us | Useful Resources

Summarize / Count / Average / Maximum / Minimum / Median / Count Unique/ Subtotal.

This command allows you to Summarize (Sum, Count, Average, Maximum, Minimum , Median, Count Unique...) your data by specifying group by columns.
For example, I want to Sum, Count, Average, Maximum, Minimum column 'Stock' group by column 'ID' . See following screenshots:


Tool location: Add-Ins →Excel Tool Box → Subtotal: Shortcuts: (Alt + X + X + B )

The sample table: summary.xlsx

 

 
Group by
Group by columns are used to order your data and then to roll it up into one row containing sub-totals for one or more columns in the worksheet.

Pivot Column

Pivot Column is the column that is displayed across the top of the pivot table.
The Group By Columns are displayed down the left hand side of the pivot table.

By default, sub-totals are displayed in the pivot table . Use this dropdown to choose a different calculation. Calculations include:

  • Sum
  • Count
  • Average
  • Max
  • Mini

Sum 'Stock' group by column 'ID' . See following screenshots:

1. Open the Subtotal pane by clicking on: Add-Ins →Excel Tool Box → Subtotal or pressing the Shortcuts: (Alt + X + X + B ).

 

2. Click on the down arrow and select 'Group by' from the drop-down list.

3. Click the list item 'ID' and The list item will be marked 'Group by' characters automatically. Here you specify column by which to create summary sub-totals for. You can add more Group by columns by clicking on list item.

Group by columns are used to order your data and then to roll it up into one row containing sub-totals for one or more columns in the worksheet.

4. Click the list item 'Stock'. Note: the list item 'Stock' will be marked as 'Group' (the last is marked by the symbol).

5. Click on the down arrow and select 'Sum' from the drop-down list.

6. The list item 'Stock' will be marked 'Sum' characters automatically.

7. Click 'OK' button.

Results:


Count 'Stock' group by column 'ID' . See following screenshots:

Results:


Max 'Stock' group by column 'ID' . See following screenshots:

Results:


Operation same as above

Min 'Stock' group by column 'ID' See following screenshots:

Average 'Stock' group by column 'ID' See following screenshots:


Crosstab / Pivot Table -- Convert Flat Table to Crosstab (Pivot Table).

This command allows you convert a one-dimensional list into two-dimensional table (Crosstab / Pivot Table) for better understanding / analyzing of your data.

If you want to create Two Dimensional Table (Crosstab / Pivot table), column 'Year' is the column that is displayed across the top of the pivot table. Column 'Fruit' are displayed down the left hand side of the pivot table. See following screenshots:

1. Open the Subtotal pane by clicking on: Add-Ins →Excel Tool Box → Subtotal or pressing the Shortcuts: (Alt + X + X + B ).

2. Click the down arrow and select 'Group by' from the drop-down list.

3. Click the list item 'Fruit' and The list item will be marked 'Group by' characters automatically.

(The Column 'Fruit' are displayed down the left hand side of the pivot table.)

 

4. Click the list item 'Year'. Note: the list item 'Year' will be marked as 'Group By' (the last is marked by the symbol).

5. Click on the down arrow and select 'Pivot Column' from the drop-down list.

6. Then the list item 'Year' will be marked 'Pivot Column' characters automatically.

(The Column 'Year' is the column that is displayed across the top of the pivot table. )

7. Click the list item 'price'.

8. Click on the down arrow and select 'Sum' from the drop-down list.

(You can use this dropdown to choose a different calculation. Calculations include: Sum, Count, Max, Min, Average. )

9. Then the list item 'Price' will be marked 'Sum' characters automatically. then Click the 'OK' button.

Results:


Also see this example:

Convert Crosstab / Pivot Table to Flat Table (List)


Another method of achieving


A plurality of content of a dictionary, the character string adding (Group by Column).

(Join several rows into one cell with separator comma, space or any other character)

For example, I want to combine (merge) multiple cells into one cell based on column 'Airways' (Group by Airways). See following screenshots:

1. Open the Subtotal pane by clicking on: Add-Ins →Excel Tool Box → Subtotal or pressing the Shortcuts: (Alt + X + X + B ).

2. Click the down arrow and select 'Group by' from the drop-down list.

3. Click the list item 'Airways' and The list item will be marked 'Group by' characters automatically.

4. Click the list item 'Aircraft Type'. Note: the list item 'Aircraft' will be marked as 'Group By' (the last is marked by the symbol).

5. Click on the down arrow and select 'Adding Strings' from the drop-down list.

6. Then the list item 'Aircraft Type' will be marked 'Adding Strings' characters automatically. then Click the 'OK' button.

7. If you want to have the merged values separated by a comma, space or any other character, you can enter the separator in the Delimiter with field . then Click the 'OK' button.

Results:

Another method of achieving


Enhanced Summary: To provide more functions, functions of species more than MS Pivot Table and Subtotal.

Use this dropdown to choose a different functions. Functions include:

  • Count of unique values in
  • Count
  • Sum
  • Average
  • Max
  • Mini
  • Median: the median of {1, 2, 3, 4, 5} is 3; the median of {1, 2, 3, 4, 5, 6} is (3 + 4) / 2 = 3.5
  • CountBlank
  • Var
  • StDev
  • AveDev: returns the average of the absolute deviations of the numbers provided.
  • DevSq :
  • Skew
  • VarP
  • StDevP
  • Mode
  • SumSq
  • GeoMean
  • HarMean
  • Kurt
  • First Quartile
  • Third Quartile
  • Percentile


Tool location: Add-Ins →Excel Tool Box → Enhanced Summary: Shortcuts: (Alt + X + X + Y )

The sample table: sum enhanced.xlsx

See following screenshots:

Count 'Name' group by column 'Name' .

1. Click the down arrow and select 'Count' from the drop-down list (Select a Function) .

2. Click the down arrow and select 'Name' from the drop-down list (Group By).

3. Click the 'Add Function >' button.

4. Click the 'Add Group By >' button.

5. Click the 'OK' button.

Results:


Count 'Name' and 'Stroe'

Sum of 'Stock'

Minimum 'Stock'

Maximum 'Stock'

Average 'Stock'

Median 'Stock'

group by column 'Name' and 'Stroe'

 

1. Click the down arrow and select 'Count' from the drop-down list (Select a Function) .

2. Click the down arrow and select 'Name' from the drop-down list (Group By).

3. Click the 'Add Function >' button.

4. Click the 'Add Group By >' button.

5. Click the down arrow and select 'Store' from the drop-down list (Group By), then Click the 'Add Group By >' button.

6. Click the down arrow and select 'Sum of' from the drop-down list (Select a Function).

7. Click the down arrow and select 'Stock' from the drop-down list (Column) , then Click the 'Add Function >' button.

4. Click the down arrow and select 'Minimum' from the drop-down list (Select a Function) , then Click the 'Add Function >' button.

5. Click the down arrow and select 'Maximum' from the drop-down list (Select a Function) , then Click the 'Add Function >' button.

6. Click the down arrow and select 'Average' from the drop-down list (Select a Function) , then Click the 'Add Function >' button.

7. Click the down arrow and select 'Median' from the drop-down list (Select a Function) , then Click the 'Add Function >' button.

8. Click 'OK' button.

Results:


Count of Unique Values in Subtotal (Pivot table)

Count of unique values in 'Name' Group by 'Store'

 

1. Click the down arrow and select 'Count of unique values in' from the drop-down list (Select a Function) , then Click the 'Add Function >' button.

2. Click the down arrow and select 'Store' from the drop-down list (Group By), then Click the 'Add Group By >' button.

3. Click 'OK' button.

Results:

Copyright © 2001-2014 Excel-Tool All Rights Reserved.

Copyright Excel-Tool All rights reserved