1. Sumif function in Excel
You usually use the sumif function in Excel to sum the values in a range that meet one criteria that you specify. For example, suppose that in a table with two columns that contains 1. Fruit 2. Price, you may want to sum only the price of apple.
2. Real example with a raw data
If you have raw data like below, where you want to sort the data. You want to see the sum of $ Amount by G/L Account, and it’s time for you to use the sumif function.
You may prepare a summary table (sorted by G/L account at the left of the table) like below first.
Thereafter, you put the sumif function under “$ Amount” column and enter the three arguments such as range, criteria, [sum_range]. three arguments in the sumif function.
Range is the range of cells that you want evaluated by the criteria that you want. In this example, it should be column N of the raw data above.
Criteria will decide which cells in the [sum_range] to be added. In this example, it should be each G/L Account in the column B of the summary table above like 7054000, 7055000, 6605000 and so on.
[sum_range] is the range of cells to be added, which should be the column G of the raw data.
Sumif function, in this case, should be filled out like below in the end.
If you understand the concept of relative/absolute references, the sumif fuction above is not that difficult to make.
Finally, if you copy the formula and paste it to the bottom of the table, you will see all the $amount well added by each G/L account.
For more easy and useful Excel tips, click the link below!