Technology Sharing

한어Русский языкEnglishFrançaisIndonesianSanskrit日本語DeutschPortuguêsΕλληνικάespañolItalianoSuomalainenLatina

1、Description of Requirement

As shown in the figure below, we now need to count the "working hours (a2)" of each person in 2022 YTD (Year To Date).

The figure below is an example of the working time data details directly exported by the system.

2、Solutions

Step 1: Determine the logic. "Extract the year from the date, and then count directly by year";

Step 2: Create auxiliary columns. Create auxiliary columns in the "Working Time List" (similar to the principle of auxiliary lines in mathematics) and extract the "Year", as shown in column H in the figure below;

Think about it: How is the auxiliary column H constructed? The answer will be revealed in "Excel Share 31"!

Step 3: Conditional summation. Use the sumifs function to sum based on multiple conditions such as name and year.

3. Specific function solution

Take the formula of cell H4 as an example:

=SUMIFS(Work Schedule!C:C,Work Schedule!B:B,C4,Work Schedule!H:H,2022)

The function effect is as follows:

4. Expand your thinking

Q1: In addition to the idea of ​​auxiliary columns, can we use nested functions to achieve this statistical goal? If so, how should we write the formula? If not, why not?

Q2: The same mathematical logic and functions are used to count the “Number of working days (b2)” data. Try to practice it yourself!

-----------------------------------------------------------Dividing line----------------------------------------------------------

suggestion:

Function nesting: the principle of separation before combination: For nested functions, it is best not to enter all functions at once (except for particularly skilled experts). All functions involved should be confirmed to be correct individually and then merged layer by layer. During the merging process, the results should be checked layer by layer to ensure they are correct, so that it is easier to locate the link where the problem occurs.