How to Calculate Labor Cost in Excel

experto.de

How to calculate subtotals in Excel 2010

Reading time: 3 minutes

tip: Mastering Excel - In no time you will get to know the new Excel from the Basics Up to the
Professional functions
.

1. Calculation of partial results using the "partial result" formula

  • Use an auto filter on your spreadsheet.
  • Stand on a blank line below your table.
  • Leave space between the formula and the last row in the table.
  • Enter the following formula:
    = partial result (function; area of ​​the table)

Explanation of the functions within the formula "partial result"

1 AVERAGE
2 NUMBER
3 NUMBER2
4 MAX
5 MIN
6 PRODUCT
7 STDEV
8 STABWN
9 SUM
10 VARIANCE
11 VARIANCES

Example of a calculation

Summation of partial results (see Figure 1): The table has an auto filter in column A (product) + B (price). Line 13 contains the formula "partial result".

Result

If you use the autofilter in this example to filter in column A (products) for the product trousers, then it calculates the subtotal for the product trousers (see Figure 2).

2. Calculation of partial results using the partial result function on the toolbar

The function can be found under Excel 2007-2010.

  • Data tab
  • Group outline
  • Partial result function

Example of a table

roadfloorSurnameelectricity
Elbestrasse1st floorBeate Scheuer444,82 €
Elbestrasse1st floorLars Eckbert286,32 €
Elbestrasse1st floorHelmut Doll506,18 €
Elbestrasse2nd FloorMarkus Eckbert429,49 €
Elbestrasse2nd FloorMeike Knoll332,34 €
Elbestrasse2nd FloorKirsten Schmitz265,87 €
North street1st floorThomas Bauer384,82 €
North street1st floorPeter Palmer316,15 €
North street1st floorSusanne Stein296,78 €
North street2nd FloorSven Sommer333,67 €
North street2nd FloorMiriam Ballweg412,70 €
North street2nd FloorKarin Hopkins283,92 €

(Source: Herdt-Verlag)

Tip: The table must be sorted in ascending order in the 1st column and does not contain any formulas or calculations, because formulas are only inserted with the "partial result" function.

How do you create a partial result using the "partial result" function on the "data" tab?

Excel 2007-2010

  • Mark the table
  • Data tab
  • Group outline
  • Partial result function
  • Select the grouping in the mask that then appears. In our example, we grouped by street. Select the "Sum" function in the "Using after" field. In our example, click on the "Current" field in the "Add partial result to" field.
  • The following options are also selected in this mask:
    • Replace existing partial results,
    • Show results below the data.

Result

The table is grouped in column A (street) and in column D (current) the current is totaled as a partial total for each street. A total is displayed below the table:

roadfloorSurnameelectricity
Elbestrasse1st floorBeate Scheuer444,82 €
Elbestrasse1st floorLars Eckbert286,32 €
Elbestrasse1st floorHelmut Doll506,18 €
Elbestrasse2nd FloorMarkus Eckbert429,49 €
Elbestrasse2nd FloorMeike Knoll332,34 €
Elbestrasse2nd FloorKirsten Schmitz265,87 €
Elbestrasse result 2.265,02 €
North street1st floorThomas Bauer384,82 €
North street1st floorPeter Palmer316,15 €
North street1st floorSusanne Stein296,78 €
North street2nd FloorSven Sommer333,67 €
North street2nd FloorMiriam Ballweg412,70 €
North street2nd FloorKarin Hopkins283,92 €
Nordstrasse result 2.028,04 €
Overall result 4.293,06 €

(Source: Herdt-Verlag)

3. Calculation of partial results using the dynamic table

Excel 2007-2010

  • Create table with column headings
    Tip: The table must not have an auto filter
  • Mark the table
  • Start tab
  • Group styles
  • Format as a table
    Click on the "Table has a heading" function

Result

  • An auto filter is inserted in the first column of the table.
  • Another tool "Table Tools" will be added to the top right of the screen.
  • This tool has 1 tab: Design (see Figure 3).
  • In this tab, click on the "Options for table format" function and click on the "Result line" option.
  • A result line is inserted below the table.
  • Click in this result line. You will then see a pull-down arrow next to the respective cell (see Figure 4).
  • With this you can set a calculation type for the respective column. The calculation corresponds to the "partial result" formula (see point 1 above).

PS: Quality management is important to us!

Please let us know how you like our post. To do this, click on the asterisks shown below (5 asterisks = very good):

PPS: Did you particularly like the article?

Support our advisor portal: