[Excel] What to do when you want to check the "number of sales per hour"! ??Technique to aggregate data by time zone in Excel

Let's consider an example of totaling the number of sales in the "●● store sales record" by time zone. The COUNTIF or COUNTIFS functions are useful when you want to count the number of cells that meet the criteria. Use the COUNTIF function when there is only one condition, and use the COUNTIFS function when there are multiple conditions. As you can see by reading the explanation that follows, the COUNTIFS function is used because there are multiple conditions when aggregating data by time zone as in this example.

The COUNTIFS function is used in the format "= COUNTIFS (search range 1, search condition 1, search range 2, search condition 2, ...)". The search range can be the same or different for each condition. Also, one of the nice things about this function is that you can use one or more search ranges and search criteria. Let's actually make a mathematical formula.

Let's aggregate the data of "before 8 o'clock" in cell G3 of the "●● store sales record" attached table. That is, the number of sales before 8:59 is totaled. Click the cell (cell G3 in this case) where you want to display the aggregated data, and enter the formula using the COUNTIFS function. Here, the search range is "B3: B32" because it is sufficient to search for the time that meets the conditions from the time data in column B (actually, the data is entered in the cell range B3: B32). The first half of the formula is "= COUNTIFS (B $ 3: B $ 32," (1)) so that the reference range does not change even if the formula is copied or moved to the cell below in this table. (Here, since it is assumed that the formula will be copied downward in this table in the subsequent operation, "$" is added in front of the line number to prevent the search range from shifting. Masu). The formula is in the middle, so continue to enter it.

Then enter your search criteria. The condition "before 8:59" is, in other words, "before 9:00". Expressing this in an Excel formula is "<9:00". Since "the value as the time is less than 9:00", when specifying it as a search condition, enclose it in half-width "" "(double quotation). Therefore, the latter half of the formula is "" <9:00 ")" (②). This is the only search condition for formulas that aggregate data before 8 o'clock, so the formula is now complete. Press the [Enter] key (③) to confirm the formula.

The calculation was done and the result was displayed in cell G3 as "1" (④). Looking at the sales record table, it is true that the number of sales at 8 o'clock is one. Click cell G3 to select it, and the formula "= COUNTIFS (B $ 3: B $ 32," <9:00 ")" you entered earlier will be displayed in the formula bar.

Now, let's continue to count the number of sales in the 9 o'clock range (that is, from 9:00 to 9:59). Copy the formula you entered in cell G3 to cell G4 (⑤).

The copied formula is a formula for totaling the number of sales before 8 o'clock, so let's change it to a formula for totaling the number of sales before 9 o'clock. There is no need to modify the search range. Modify the search criteria. "9 o'clock" can be expressed by combining the two conditions of "after 9:00" and "before 9:59". First, let's formulate the first condition "after 9:00". This condition can be expressed as ""> = 9:00 "". Select cell G4 and then click the formula bar (⑥) to modify the search condition in the latter half of the formula to ""> = 9:00 "" (⑦). The formula you entered in cell G3 did not include 9:00, but this formula does include 9:00, so the inequality sign is followed by an equal sign (=).

Next, add a second search range and search criteria to the formula. Since the search range is the same cell range, enter ", B $ 3: B $ 32," (⑧) after the search condition of the formula above.

Enter the second search criteria. The second search condition for expressing "9 o'clock" was "before 9:59 (that is, before 10:00)". This can be expressed as "" <10:00 "" in the same way as the formula entered in cell G3. Enter "" <10:00 "" (⑨) after the above formula. Now that the formula is complete, press the [Enter] key (⑩).

In cell G4, the number of sales in the 9 o'clock range was displayed as "4" (⑪). Click cell G4 to select it and the formula bar will display the formula "= COUNTIFS (B $ 3: B $ 32,"> = 9:00 ", B $ 3: B $ 32," <10:00 ")" ..

Similarly, if you copy the formula and correct only the time part, a table that aggregates the number of sales by time zone will be completed (⑫).

In this way, when aggregating data by time zone, the point is to read "○ o'clock" as "after ○: 00, before ○: 59" and express it in a mathematical formula.