Link Search Menu Expand Document
Excel toolbox

6.1 Several advanced formulas

04-Oct-2022 9 mins Share on social networks


TABLE OF CONTENTS

6.1.1 Adding cells that meet a criterion

It is possible to add cells in a given range that correspond to a given criterion. This criterion can be a number, a word, the value of a cell or a comparison. The =SUMIF function should be used for this.

The Excel =SUMIF function has a syntax that contains three parameters, one of which is optional:

=SUMIF(range of cells; condition to be met; range to be added)

  • The range of cells: This argument contains the range of cells to sum and is mandatory.
  • The condition to be met: This argument contains the condition that determines which cell will be taken into account in the sum. The argument can contain a number, an expression, a cell reference, a date, or a text string. This argument is mandatory.
  • Range to sum: Actual cells to sum, if you want to sum cells other than those specified in the range argument. This argument is optional.

Discover below two example cases using SUMIF:

a. Summing numbers according to their value

This is the simplest use of the SUMIF function. You simply sum values that correspond to a value or comparison. For example, the sum of the cells that contain the number 100. Or the sum of numbers smaller than 1,000, etc. In this case, you only need the first two arguments of the SUMIF function, i.e., the range of cells and condition to be met. The third argument (range to sum) will not be used.

image info

b. Summing numbers according to their corresponding values

In this case, you sum the values that correspond to a condition that is in another range of cells. Often, you have one column with the values to be tested and another column with values to sum in case the condition is met. You thus need to use all three arguments of the SUMIF function.

image info

Keep in mind

Even if the SUMIF formula is rather easy to use, there are a number of points to bear in mind that can lead to calculation errors:

  • The argument containing the counting condition (condition to be met) must imperatively be enclosed in quotation marks, even if the conditions are numeric (“5”) or expressions that contain a comparison operator (“< 10”).
  • If one of the cells to be tested contains an error (#VALUE!), the SUMIF function will return an error as well.
  • If you want to use a textual condition that uses a long string of characters (more than 255 characters), the function will return an error instead of the result. But it is possible to circumvent this limitation: just separate the long text into smaller parts (with less than 255 characters each) that you can then “recompose” thanks to the concatenation operator (&).

Example: = SUMIF(A1:A1000;”first part” & “second part”;B1:B1000)

6.1.2 Performing logical comparisons

The IF function in Excel estimates a value (usually the value of a cell) based on a condition and returns a different value depending on whether the condition is met or not.

The =IF function has a syntax that contains three parameters:

=IF(condition; Value if condition is met; Value if condition is not met)

  • The condition: It assesses two values against each other using one of the logical operators: = equal, > greater than, >= greater than or equal to, < less than, <= less than or equal to, <> different from.
  • The Value if condition is met: This is the value (or formula) that the IF function will return if the condition is TRUE. This argument is optional.
  • The Value if condition is not met: This is the Value (or formula) that the IF function will return if the condition is FALSE. This argument is optional.

Values returned as a response can contain text (such as Yes or No), a number, the contents of a specific cell, or even another formula (such as another IF function – see the paragraph on nested IF functions below.

image info

If you need to put multiple IF functions one into the other, you will need to use nested IF function.

Let’s take a practical example where you want to create, from an Age variable in column A, a new variable with age ranges related to each of the individuals in your database:

  • Under 20 years of age
  • From 20 to 49 years
  • 50 years and older

The formula is as follows:

=IF(A1<20;”Under 20 years of age”;IF(AND(A1>19;A1<50);”from 20 to 49 years”;IF(49<A1;”50 years and older”;”))

Keep in mind

  • It is possible to nest a very large number of functions, but to ensure maximum efficiency and readability, avoid using more than 10.
  • Feel free to use the carriage return (ALT + Enter) in the formula bar to make them more readable. Regarding the below formula, an equivalent would be:
    • IF(A1<20,”Under 20 years of age”,
    • IF(AND(A1>19,A1<50),”from 20 to 49 years”,
    • IF(49<A1,”50 years and older”,”))

The following exercise is only available on the French page at this stage- translation coming soon!

In order to practice using the IF function, by nesting in other functions, here is a practical exercise (Bonus 3) using clean data from the case study - developed by CartONG as part of the Data Analysis Toolbox publication. A more complete presentation of this case study can be found in the “Data Analysis Toolbox” – Part “7 The case study ”.

In this example, we propose to reconstruct a dataset indicating the duration of food security assistance as well as the size of households, among those having received this aid. To do this, you will use the nested IF and VLOOKUP functions.

Use the [“Excel Toolbox - Analysis 4.5 - Practice exercise - V1 - Statement”] file to practice using the IF function.

In case of difficulties, or to compare your results with those of the case study, refer to the file “Excel Toolbox - Analysis 4.5 - Practical exercise - V1 – Solutions”. </div

6.1.3 The colour of the brackets

It’s not always easy to have the right number of brackets in the right places. And in formulas with nested IF functions, this problem occurs regularly. Click in the Formula bar that contains your formula, Excel will display, using various colours, the cells used in your formula.

But not only! The software also displays pairs of corresponding brackets with different colours for each pair. It is therefore easier to navigate. And this also applies to other complex formulas in Excel!

6.1.4 Finding a specific value in an array

To search for a specific value in a database, you can use a combination of both =MATCH and =INDEX functions.

If the MATCH function returns the row number, the syntax is as follows:

=INDEX(array; MATCH(searched value; search range; 0); column number)

If the INDEX function returns the column number, the syntax is as follows:

=INDEX(array; row number; MATCH(searched value; search range; 0))

Let us take a practical example: The goal is to find the identifier and the age of the beneficiary of the country being sought. The user must be able to enter the city in the cell below the Sought Country and then see the search result in the corresponding part, in E33 and F33:

image info

Select the INDEX function:

  • In Matrix, enter the range of cells of the table: A29:C26
  • In ROW, do not enter anything at this time (the MATCH function that will calculate the row number based on the search will be inserted here)
  • In COLUMN, enter the column of the table that contains the data to return. In this example, the purpose is to display the identifier, so it is column 1.
  • Now go to ROW and add the MATCH function. The purpose of the MATCH function is therefore to return the row number of the given country reference. In Lookup_Value, enter the value whose position, here, the country, is to be searched for in E9.
  • In Lookup_Table, enter the range of cells in which the function will search for the country ID: B29:B36
  • In Type, enter 0 to find the exact value.

image info

In E33, the formula returns the identifier associated with the country mentioned in E29.

6.1.5 The SumProduct function

SumProduct is an extremely powerful formula, it enables you - as shown here (section 7.5) - to calculate the sum of the products of ranges or matrices. (By matrices, understand arrays/tables, it is an approximation, but which will allow you to better understand the logic).

To better understand what this is for, check out the following example:

In the case of a humanitarian crisis response, several NGOs provide their support to a Camp composed of different areas. Each NGO targets a certain number of beneficiaries. Your goal is to calculate all the beneficiaries that the two NGOs in Area B cover in the field of water, sanitation and hygiene (WASH). You could of course use two filters and then calculate the sum once the filters are applied.

But this presupposes that you only want to carry out this operation, that your criteria are not very numerous and that you do not have to perform this operation regularly.

If this is the case, we recommend the following formula:

SUMPRODUCT((Tableau1[Area]=G1)*(Tableau1[Domain]=F1)*Tableau1beneficiaries])

image info