In this second part, we will learn the most basic Calc formulas namely IF, SUMIF and COUNTIF after previously we learned about SUM, COUNT and AVERAGE by examples. These are kinds of formula which often used for decision making, such as making student's grade based on score, and stuffs around that. This tutorial is part of our LibreOffice Calc learning by practices for the formulas often used in jobs. Let's start!

Subscribe to UbuntuBuzz Telegram Channel to get article updates.

Dataset

Type this is the dataset and save the document as calc-02-if-sumif-countif.ods for this exercise.

Notice that whole table spans across B2:E9, NAME data spans across C3:C9, GENDER data D3:D9, and SCORE data E3:E9. Finally, create three new sheets named IF, SUMIF, and COUNTIF.

4. IF

In the IF sheet, do:

• Type the dataset above. 
• Add a new column at F3 named GRADE.
• Put the cursor at E3.
• Type the formula =IF(F3>=60,"PASS","FAIL")
• Press Enter.
• You made one grade.
  
• Drag down the formula to end row of GRADE column.
• You made all grades.
  

This formula made student grading based on score. If the score is 60 or above, he/she passes, otherwise he/she fails. Thus, Silvie and Gunawan failed, while Abi and the rest passed.

5. SUMIF

Switch to SUMIF sheet, do:

• Type the dataset above. 
• Create a cell at D11 named MALES TOTAL SCORE.
• Create another cell at D12 named FEMALES TOTAL SCORE.
• After first total score, type the formula =SUMIF(D3:D9,"MALE",E3:E9)
• After second total score, type the formula =SUMIF(D3:D9,"FEMALE",E3:E9)
• You made student's total scores based on gender.
  

This example made total score based on gender. The formula requires two columns, GENDER and SCORE, to be compared and processed. Thus, 251 is the total score of only male students, while 215 is of female.

6. COUNTIF

Switch to COUNTIF sheet, do:

• Type the dataset above. 
• Create a cell at C11 named NUMBER OF MALE STUDENTS.
• Create another cell at C12 named NUMBER OF FEMALE STUDENTS.
• After the first student number, type the formula =COUNTIF(D3:D9,"MALE")
• Similarly to the second, type =COUNTIF(D3:D9,"FEMALE")
• You made number of students based on gender. 
  

This example is the simplest of all here as it just counts the quantity of male and female students. Thus, male students are four and female students are three. As simple as that.

Animations

To help you practicing, here's short animations to do the exercises above of IF, SUMIF and COUNTIF.

IF:

SUMIF:

Now you should have the basic skills of IF, SUMIF and COUNTIF. Next time, you will learn about PROPER, UPPER and LOWER formulas. Happy learning. See you next time!

To be continued...