Practice question 1/Sum up cells that includes a specific text

【Practice question 1 : Question】

The table below shows the number of employees in each office.
In each region, there is a department that supervises the offices (branches) named the ‘supervisory’ department.
Calculate the number of employees in the ‘supervisory’ department.
The total number of employees in this list (cell D24) is already calculated for you. (Displayed in blue writing.)

▶Download data in Excel

Hints!

Even though we do not know what function to use, it will not be a problem if we can find the correct function by searching the internet. Therefore, learning how to type the relevant keywords in the search engine is important. Other than using the search box in this website, search engines such as Google and Yahoo! can be helpful. In this case, keywords like below will lead us to get what we wish for, so when having trouble, use the internet.

【Keywords for search】
Find the function that sums up cells that meet a specified condition・・・Excel condition sum function
Search box for this website
※There are more keywords other than the above.

 

【Practice question 1 : Answer】

Below is the answer to practice question 1.

※The answer (number of employees from ‘Supervisory’) is in red.

【Cell】 【Formula】【Value】    
D19 : =SUMIF(C7:C19,”Supervisory”,D7:D19)⇒ 18      

【Cell】 【Formula】【Value】           
D21 : =SUM(D7:D19) ⇒   294            

The formula in blue is only for reference. This is not the main topic in this section.

▶Download answers in Excel

【Practice question 1 : Explanation】

To calculate the sum for cells that meet a specific condition, we use the SUMIF function.

=SUMIF(range, criteria, [sum_range])
[range] = Select cell range to find if it meets condition.
[criteria] = Input condition (number or text).
[sum_range] = Select cell range to find the sum for.

Like the diagram below, the function will search within the [range] (between cells C7~C19) for cells that meet the
[criteria] (The office name to be ‘Supervisory’), and will pick out the significant cells from the
[sum_range] (cells D7~D19) and sum up cells D7, D12 and D15.
The number of employees in the ‘Supervisory’ department is ‘18‘ people.

 

【Reference】Be careful that the [range] in the SUMIFS formula does not shift!

The position of the [range]  and [sum_range] is extremely important when using the SUMIF function.
Select the same row like the diagram below.

Below is the same table, but the columns and rows are switched.
In this case, make sure that the columns match.

 

Example of a range selectetion error

When using the SUMIF function, if the [range] and the [sum_range] is not in the correct position (correct row or column) like below in the green box,
it will pick up the cells that meet the condition and sum up the values in the relative position which will lead to an incorrect result.
Even if the function seems to show no error, the results might be different from what you expect, so make sure that the [range] and the [sum_range] match with each other.

【Practice question 1 : Wrap-up】

To calculate the sum of cells that meet a specified condition, we use the SUMIF function.

=SUMIF(range, criteria, [sum_range])
[range] = Select cell range to find if it meets condition.
[criteria] = Input condition (number or text).
[sum_range] = Select cell range to find the sum for.

The function that we used for this practice question is explained in detail in the following link.
Feel free to check and revise with our website.

 

Practice question 2/Sum up cells that ‘include’ a specified text

【Practice question 2 : Question】

The table below shows the number of employees in each office.
Calculate the number of employees working in a ‘branch’.
The total number of employees in this list (cell D19) is already calculated for you. (Displayed in blue writing).

▶Download data in Excel

Hints!

Even though we do not know what function to use, it will not be a problem if we can find the correct function by searching the internet. Therefore, learning how to type the relevant keywords in the search engine is important. Other than using the search box in this website, search engines such as Google and Yahoo! can be helpful. In this case, keywords like below will lead us to get what we wish for, so when having trouble, use the internet.

【Keywords for search】
Find the function that sums up cells that meet a specified condition・・・Excel condition sum function
Find the way to assign a condition・・・Excel condition assign sum function
Search box for this website
※There are more keywords other than the above.

 

【Practice question 2 : Answer】

Below is the answer to practice question 2.

※The answer (number of employees from a ‘branch’) is in red.

【Cell】 【Formula】   【Value】 
D19 : =SUMIF(B7:B16,”*branch*”,D7:D16)  ⇒   230  

【Cell】 【Formula】【Value】           
D21 : =SUM(D7:D16) ⇒   276            

※The formula in blue is only for reference. This is not the main topic in this section.

▶Download answers in Excel

【Practice question 2 : Explanation】

To calculate the sum for cells that meet a specific condition, we use the SUMIF function.

=SUMIF(range, criteria, [sum_range])
[range] = Select cell range to find if it meets condition.
[criteria] = Input condition (number or text).
[sum_range] = Select cell range to find the sum for.

Like the diagram below, the function will search within the [range] (between cells C7~C16) for cells that meet the
[criteria] (includes the word ‘branch’ in location name), and will pick out the significant cells from the
[sum_range] (between cells D7~D16) and sum up cells D7, and D12~D15.
The number of employees working in a ‘branch’ is ‘230‘ people.

【Reference】How to use the wildcard character ‘*’.

In this question we wanted to find location names that have the word ‘branch’ in them.
For this, ‘wildcard characters’ become useful.
What is a wildcard character?
Wildcard characters are useful to search for words which include the specified character, by adding it in the front, the back, or both front and back.

Symbol Definition
* Allows more than one character
? Allows one character

How to use the wildcard character
When you want to find cells with a specific word like ‘any word + branch + any word’ , we use the ‘*’ symbol. →”*branch*”
=SUMIFS(D7:D16,B7:B16,“*branch*”,C7:C16)

To count the number of cells which include a certain character or word, we use * as the arbitrary character.
Example) To find cells with the word ‘branch’:

When ‘branch’ comes in the beginning
Example) Branch office
=SUMIF(range,”branch*,”[sum_range])
When ‘branch’ comes in between
Example) Texas branch office
=SUMIF(range,”*branch*,”[sum_range])
When ‘branch’ comes in the end
Example) Utah branch
=SUMIF(range,”*branch,”[sum_range])

【Practice question 2 : Wrap-up】

To calculate the sum for cells that meet a specific condition, we use the SUMIF function.

=SUMIF(range, criteria, [sum_range])
[range] = Select cell range to find if it meets condition.
[criteria] = Input condition (number or text).
[sum_range] = Select cell range to find the sum for.

The function that we used for this practice question is explained in detail in the following link.
Feel free to check and revise with our website.

データをカウントする
Describing the SUMIF function using the wildcard specifying ‘including ~’.

 

Practice question 3/Sum up cells that does not include a specified text

【Practice question 3 : Question】

The table below shows the number of employees in each office.
Calculate the number of employees that do not work in a ‘service office’.
The total number of employees in this list (cell D19) is already calculated for you. (Displayed in blue handwriting).

▶Download data in Excel

Hints!

Even though we do not know what function to use, it will not be a problem if we can find the correct function by searching the internet. Therefore, learning how to type the relevant keywords in the search engine is important. Other than using the search box in this website, search engines such as Google and Yahoo! can be helpful. In this case, keywords like below will lead us to get what we wish for, so when having trouble, use the internet.

【Keywords for search】
Find the function that sums up cells that meet a specified condition・・・Excel condition sum function
Find the way to assign a condition・・・Excel condition assign sum function
Search box for this website
※There are more keywords other than the above.

 

【Practice question 3 : Answer】

Below is the answer to practice question 3.

※The answer (number of employees that do not work in a ‘service office’) is in red.

【Cell】 【Formula】   【Value】 
D19 : =SUMIF(C7:C16,”<>*service office”,D7:D16)  ⇒   250  

【Cell】 【Formula】【Value】           
D21 : =SUM(D7:D16) ⇒   276            

※The formula in blue is only for reference. This is not the main topic in this section.

▶Download answers in Excel

 

【Practice question 3 : Explanation】

To calculate the sum for cells that meet a specific condition, we use the SUMIF function.

=SUMIF(range, criteria, [sum_range])
[range] = Select cell range to find if it meets condition.
[criteria] = Input condition (number or text).
[sum_range] = Select cell range to find the sum for.

Like the diagram below, this function will search within the [range] (between cells C7~C16) for cells that meet the
[criteria] (does not include the word ‘service office’ in location name), and will pick out the significant cells from the [sum_range] (between cells D7~D16) , and sum up cells D7, D9, D12~D15.
The number of employees that do not work in a ‘branch’ is ‘250‘ people.

 

【Reference】The symbols that we can use for search

Symbols Definition
< Smaller than the selected number
<= Equal to or smaller than the selected number
> Bigger than the selected number
>= Equal to or bigger than the selected number
<> Not equal to selected number (besides/other than)

【Practice question 3 : Wrap-up】

To calculate the sum for cells that meet a specific condition, we use the SUMIF function.

=SUMIF(range, criteria, [sum_range])
[range] = Select cell range to find if it meets condition.
[criteria] = Input condition (number or text).
[sum_range] = Select cell range to find the sum for.

The function that we used for this practice question is explained in detail in the following link.
Feel free to check and revise with our website.

データをカウントする
Describing the SUMIF function and the use of symbols to define ‘not ~’.

Practice question 4/Multiple condition/using AND

【Practice question 4 : Question】

The table below shows the number of employees in each office, and if there is a company owned house.
Use the SUMIF function and calculate the number of employees in the ‘Supervisory’ department who work in a location that has a company owned house.
(The SUMIF function itself does not have he function to describe multiple conditions. Try to remember what data you need to calculate the above using the SUMIF function.)
The total number of employees in this list (cell D19) is already calculated for you. (Displayed in blue writing).

▶Download data in Excel

Hints!

We cannot apply multiple condition with the SUMIF function. Therefore, we must create a conditional formula outside the function. Create a new column (column F), and use the AND function to create a conditional formula. Then, use the SUMIF function to calculate the number of employees.

Hints!

Even though we do not know what function to use, it will not be a problem if we can find the correct function by searching the internet. Therefore, learning how to type the relevant keywords in the search engine is important. Other than using the search box in this website, search engines such as Google and Yahoo! can be helpful. In this case, keywords like below will lead us to get what we wish for, so when having trouble, use the internet.

【Keywords for search】
Find the function that sums up cells that meet a specified condition・・・Excel condition sum function
Find the way to use the AND function・・・Excel match and function
Search box for this website
※There are more keywords other than the above.

 

【Practice question 4 : Answer】

Below are the answers to question 4.

※The answers of the number of employees in ‘Supervisory’ and in a location with a company owned house is in red, and
the results for the AND function in column F are in green.

【Cell】 【Function】   【Value】
F7  : =AND(C7=”Supervisory”,E7=”Yes”)  ⇒    TRUE 
F8  : =AND(C8=”Supervisory”,E8=”Yes”)  ⇒   FALSE 

【The same goes with F9~F14】

F15 : =AND(C15=”Supervisory”,E15=”Yes”) ⇒    TRUE 
F16 : =AND(C16=”Supervisory”,E16=”Yes”) ⇒   FALSE 

【The same goes with F17~F19】

【Cell】 【Function】   【Value】
E24 : =SUMIF(F7:F19,TRUE,D7:D19) ⇒   14 

▶Download answers in Excel

【Practice question 4 : Explanation】

There are a few steps to this question.

(1) First of all, the we can not apply multiple conditions with the SUMIF function, so we create a new column (column F) and use the AND function to check if it meets the two conditions.

This time we use the ‘AND function‘.
We use the AND function in a situation where we have two or more conditions and want to know if there are any cells that meet both.

=AND(logical1, logical2, [logical3]…)
[logical1] = Set up the 1st condition.
[logical2] = Set up the 2nd condition.
[logical3…] = Not obligatory. Set up the 3rd condition.
【Reference】For more information on the AND function, click here.

In this question we want to know the number of employees that are in the ‘Supervisory’ department and that work in locations ‘with a company owned house’.
For each row, we will check if they meet the two conditions using the AND function.

【If it meets all of the conditions】: The AND function will return a ‘TRUE‘.
For this case, if column C is ‘Supervisory’ and if there is a ‘Yes’ in column E, then column F should show a ‘TRUE‘.
Cells F7 and F15 meet the two conditions.
【If it does not meet all of the conditions】 : The AND function will return a ‘FALSE‘.
For this case, if if column C and column F do not meet the conditions, then column F should show a ‘FALSE‘.
Cells F8~F14 and F16~F19 do not meet both conditions.

The diagram below shows an example for cell F8 and F15.

(2) Secondly, we use the SUMIF function and use column F to count the sum of all cells that apply.

We used the AND function to show the word ‘TRUE‘ when both conditions are met,
so in cell E22, we type the formula =SUMIF(F7:F19,TRUE,D7:D19).
The number of employees in the ‘Supervisory’ department and that work in a location with a company owned house is ‘14‘ people.

【Practice question 4 : Wrap-up】

To calculate the sum of cells that meet a specific condition, we use the SUMIF function.
Like in【Practice question 4】, even though there are multiple conditions we can combine it with other functions and use the SUMIF function without a problem.

=SUMIF(range, criteria, [sum_range])
[range] = Select cell range to find if it meets condition.
[criteria] = Input condition (number or text).
[sum_range] = Select cell range to find the sum for.

The function that we used for this practice question is explained in detail in the following link.
Feel free to check and revise with our website.

データをカウントする
Describing the SUMIF function and how to use it for multiple conditions.