Contents
Formulation of the problem
Suppose we have the following table with data on revenue and plan fulfillment for several cities:
It is necessary to sum up the revenue for all cities or, which is approximately the same, calculate the average percentage of the plan for all cities.
If you solve the problem “on the forehead”, you will have to enter a long formula with enumeration of all cells, because it is impossible to select the entire range at once with one movement:
If the number of cities in the table is more than a couple of dozen, then this method begins to make you sad, and you can easily make a mistake when entering the formula. How to proceed? There are ways to do it more gracefully.
Method 1. The SUMIF function and its analogs for selective summation by condition
If the table has a column with a feature that can be used for selective summation (and in our case it is column B with the words “Revenue” and “Plan”), then you can use the function SUMMESLI (SUMIF):
The first argument of this function is the range of cells to be checked, the second is the selection criterion (the word “Revenue“), the third is a range of cells with numbers to sum.
Starting with the version of Excel 2007, the basic set also has a function HEARTLESS (AVERAGEIF), which does not calculate the sum, but the arithmetic mean by condition. It can be used, for example, to calculate the average percentage of the plan completed. You can read more about all the selective summation functions in this article with a video tutorial. The disadvantage of this method is that the table must have a separate column with a sign, and this is not always the case.
Method 2. Array formula to sum every 2nd, 3rd… Nth row
If there is no convenient separate column with a sign for selective summation or the values in it are non-constant (somewhere “Revenue« and where «Revenue”, etc.), then you can write a formula that will check the row number for each cell and sum only those where the number is even, i.e. multiple of two:
Let’s take a closer look at the formula in cell G2. It is better to “read” this formula from the middle outwards:
- Function LINE (ROW) returns the row number for each cell in turn in the range B2:B15.
- Function THE REST (MOD) calculates the remainder of dividing each resulting row number by 2.
- Function IF (IF) checks the remainder, and if it is equal to zero (i.e., the line number is even, a multiple of 2), then displays the contents of the next cell, or, otherwise, does not display anything.
- And finally the function SUM (SUM) sums the entire set of values returned by IF, i.e. sums every 2nd number in the range.
- This the formula must be entered as an array formula, i.e. after typing it, you need to press not Enter, but a combination Ctrl + Alt + Enter. Curly brackets do not need to be typed from the keyboard, they will be added to the formula automatically.
To enter, debug and generally understand the operation of such formulas, you can use the following trick: if you select a fragment of a complex formula and press the key F9, then Excel will calculate the selection right in the formula bar and display the result. For example, if we highlight the function STRING(B2:B15) and press F9, then we will see an array of row numbers for each cell in our range:
And if you select a fragment REMAIN(ROW(B2:B15),2) and press F9, then we will see an array of the results of the function THE REST, i.e. remainders after dividing line numbers by 2:
And finally, if you select a fragment ЕСЛИ(ОСТАТ(СТРОКА(B2:B15);2)=0;B2:B15) and click on F9, then we will see what the function actually sums up SUM in our formula:
Value LYING (FALSE) in this case, are interpreted by Excel as zero, so we end up with the sum of every other number in our column.
It is easy to see that instead of the summation function, any others can be substituted into this construction, for example, the functions MAX (MAX) or MIN (MIN) to calculate the maximum or minimum values, etc.
If new lines may appear above the table in the future (header, beautiful heading, etc.), then it is better to slightly modernize the formula for greater versatility:
Method 3. BDSUMM function and a table with a condition
Array formulas from the previous method are a beautiful thing, but they have a weak point – speed. If your table has several thousand rows, then such a formula can make your Excel “think” for a few seconds, even on a powerful PC. In this case, you can use another alternative – the function BSSUMM (DSUM). Before use, this function requires a little refinement, namely, the creation of a miniature table with a selection condition in any suitable free space on our sheet. The title of this table can be anything (the word “Condition» in E1), as long as it does not match the headers from the data table. After entering the condition in cell E2, the word will appear TRUE (TRUE) or LYING (FALSE) – do not pay attention, we will need the formula itself from this cell, expressing the condition, and not its result. After creating a table with a condition, you can use the function BSSUMM (DSUM):
Method 4. Summation of every 2nd, 3rd… Nth row
If you need to sum not individual cells, but entire rows, then you can easily implement this with an array formula similar to method 2:
Since the function SUMPRODUCT (SUMPRODUCT) automatically converts its arguments to arrays, then in this case there is no need to even press Ctrl + Shift + Enter.
- Calculation of the sample sum, average, quantity, etc. one or more conditions.
- What are array formulas, how to enter them, how they work