[Excel] How to summarize the sales amount for each condition with the "SUMIF" function of Excel -How to use Excel that you can't hear now -Window forest

How to use Excel that can't be heard anymore

「SUMIF」関数を使って、条件ごとにデータを集計する

I want to aggregate data under specific conditions!

　In various situations in business, I think that Excel uses Excel to record and manage data.But are you losing it without knowing how to handle the collected data?

　As follows, there is a table (①) that records the order status of lunch boxes for one week, and the number of orders and sales of daily lunches are managed in this table.If you can use this table to aggregate sales for each type of lunch, it will be useful for sales analysis and future menu development.

1週間のお弁当の注文状況を記録した表（①）で管理

　It is convenient to use the "SUMIF" function to find the total sales amount for each type of lunch.This time, I will explain how to aggregate data for specific conditions using the "Sumif" function.

Find the total sales amount for each type of lunch

　In the next "Bento Order Management Table" (①), let's find the total sales amount for each type of lunch.The table to enter the aggregation result is separately created on the right side of the seat (②).

集計結果を入力する表は、別途、シートの右側に作成（②）

　This time, I use a function called "SUMIF" function.A function that finds the total data that meets the specified conditions, and the format is as follows.

SUMIF関数の書式

　The first argument specifies the cell range that searches for a value that matches the conditions.The second argument specifies the search conditions, and the third argument specifies the cell range that requires the total.

　Let's create a mathematical formula.First, the cell i3 is the sum of the "hamburger lunch" sales.With cell i3 (③) selected, click the [Motor] tab (④) → [Insert function] (⑤).

セルI3（③）を選択した状態で、［数式］タブ（④）→［関数の挿入］（⑤）をクリック

　The [Insert function] dialog box is displayed. Enter "SUMIF" (⑥) in the [Function Search] column, and click [Stake] (⑦).

［関数の検索］欄に「SUMIF」（⑥）と入力し、［検索開始］（⑦）をクリック

　The search result is displayed in the [Function Name] column. Select [SUMIF] (⑧) and click [OK] (⑨).

［SUMIF］（⑧）を選択して［OK］（⑨）をクリック

　Then, the [Function Arrangement] dialog box will be displayed.Use this dialog box to enter the argument of the "Sumif" function.In the first argument, the cell range that searches for a value that matches the condition is specified (the cell range in which the names of the lunch box are entered), so input "B4: B17" (⑩) in the [Range] column.To do.Alternatively, if you drag (⑪) of the cell range "B4: B17" on the sheet, it will be automatically entered as "B4: B17".

［範囲］欄に「B4:B17」（⑩）と入力。または、シート上でセル範囲「B4:B17」をドラッグ（⑪）する

　Change "B4: B17" to absolute reference so that the cell range referenced when you copy the formula later is not shifted.Press the [F4] key once to change from "B4: B17" to "$ B $ 4: $ B $ 17" (⑫).

［F4］キーを1回押すと、「B4:B17」から「$B$4:$B$17」（⑫）に変更されます

　Continue, specify the search value for the second argument.Here, the cell "H3" (⑬) entered as "Hamburger lunch" is specified in the [Search Condition] column.

「ハンバーグ弁当」と入力されたセル「H3」（⑬）を、［検索条件］欄に指定

　Finally, specify the cell range (here, the total amount column here) for the third argument.Enter "E4: E17" directly in the [Total range] column, or drag the cell range "E4: E17" on the sheet.As mentioned earlier, the absolute reference is "$ $ 4: $ E $ 17" (⑭) so that the cell range referenced when copied the formula is not shifted.

数式をコピーしたときに参照するセル範囲がずれないように、絶対参照で「$E$4:$E$17」（⑭）とします

　The advantage of using this [Hand Arguments] dialog box is that the results can be confirmed while creating mathematical formulas (⑮).It is encouraging when you are not confident in the formula or if you use the first function.After entering all arguments, click [OK] (⑯).

　When you return to the sheet, the cell "i3" automatically creates a formula, and the calculation result is displayed (⑰).

セル「I3」には自動的に数式が作成され、計算結果が表示（⑰）

　Use the autophil to copy the formula created in the cell "i3" to other cells in column I.Drag (⑱) the fill handle displayed at the lower right of the cell "i3" to the cell "i8".

セルI3の右下に表示されるフィルハンドルをセル「I8」までドラッグ（⑱）

　Total results were displayed in all cells in column I.Set a digit -separated comma to complete (⑲).

桁区切りのカンマを設定して完成（⑲）

Use the data under specific conditions to use it for sales analysis

　This time, we explained how to aggregate data for specific conditions using the "Sumif" function.If a value can be collected for each specific condition in this way, would it be useful for sales analysis and future management decisions?Please use it.