Google Sheet Key functions

There are countless functions in google sheet, but there are some key functions to help you get familiar with the platform. Once you’ve mastered these, you’ll have no problem using variations of basic Google Sheets functions .

SUM: use to add up multiple numbers and return their sum

• =SUM(value1, [value2, …])
• value1: first value to sum
• Value2: all other numbers and/or cells

COUNT & COUNTA

• COUNT: return total cells with numbers only
  • =COUNT(value1, [value2, …])
  • value1: first value or range to check
  • value2: other values or end range to use for counting
  • 

• COUNTA: count all non-empty cells with text, numbers, dates, ….
  • =COUNTA(value1, [value2, …])
  • value1: first value or range to check
  • value2: other values or end range to use for counting
  • 

SUMIF & COUNTIF

• SUMIF: return sum with specific criteria
  • SUMIF(range, criterion, [sum_range])
  • range: scan for values related to the criterion – required
  • criterion: to apply to the range – required
  • sum_range: the range to add up records from if it’s different from first range – Optional
  • ​​

• COUNTIF: return sum with specific criteria
  • COUNTIF(range, criterion)
  • range: range to count – required
  • criterion: what consider for counting – required
  • 

AVERAGE: return sum of all numbers divided by their count

• =AVERAGE(value1, [value2, …])
• value1: first value to sum
• Value2: all other numbers and/or cells
• 

MIN, MIX

• MIN: return minimum number
  • =MIN(value1, [value2, …])
  • 

• MAX: return maximum number
  • =MIN(value1, [value2, …])
  • 

IF: use with conditions and return results accordingly

• =IF(logical_expression, value_if_true, value_if_false)]
• 

AND, OR: both are used with IF

• AND: check if all values are logically correct
  • =IF(AND(condition1,condition1),”result1″,”result2″)
  • 

• OR: check if any provided conditions are true
  • =IF(OR(condition1,condition1),”result1″,”result2″)
  • 

CONCATENATE: used to merge several cells into one without losing data

• =CONCATENATE(string1, [string2, …])
• 

TRIM: is used to trim all the trailing space 

• =TRIM(text)
• 

TODAY & NOW

• TODAY: show today’s date
• NOW: return both the today’s date and the current time
  • =TODAY()
  • =NOW()
  • 

DATE: converts whatever day, month, and year into a date format

• =DATE(year, month, day)

Exercise

Use google sheet functions to find solutions to the question below. Write down the function and its result. 

Example of the answer is SUM(B2, B5).

1. How many last names are the same?
2. What is the minimum age among those students?
3. What is the maximum age among those students?
4. Calculate the total score of each student.
5. Calculate the average score of each student.