Excel操作：如何同时匹配日期与人员并统计对应出现次数

First, let's restate your tables for clarity:

Table 1 (Raw Data) #

Table 2 (Target Summary Table) #

To populate Table 2 with the count of each person's occurrences on each date from Table 1, the best approach is to use the COUNTIFS function (available in Excel, Google Sheets, and most spreadsheet tools). This function lets you count rows that match multiple criteria—in this case, matching both the date and the person.

Step-by-Step Formula Implementation #

1. In Table 2, select the first empty cell (e.g., the cell under 01/01/2020 next to PERSON1).
2. Enter this formula:

   =COUNTIFS(Table1!$A:$A, $B$1, Table1!$B:$B, $A2)
   

   Let's break down what each part does:
   • Table1!$A:$A: The range of dates in Table 1 (adjust this to match your actual column range, e.g., Table1!A:A if Table1 starts at column A).
   • $B$1: The date header in Table 2 (the $ signs lock this reference so it doesn't shift when you drag the formula across columns).
   • Table1!$B:$B: The range of person names in Table 1.
   • $A2: The person name in the current row of Table 2 (the $ locks the column, so it stays pointed to the person column as you drag across dates).
3. Drag the formula:
   • Drag it horizontally across all date columns to fill counts for PERSON1 on every date.
   • Then drag the entire row of formulas down to apply it to PERSON2 and PERSON3.

Example Result #

After applying the formula, Table 2 will look like this:

Note: If a date/person combination doesn't exist in Table 1, the formula will return 0, which is exactly what we want for empty entries.

内容的提问来源于stack exchange，提问作者MDUB77