Top 7 Google Sheets Formulas Every Beginner’s Should Know

By /

Google spreadsheets, now becoming the most popular data management application that you can use to analyze, predict and manipulate data effectively and efficiently. This is fully open-source cloud base software and no need to install it on your computer.  Most of the time you must deal with un formatting datasheet & it doesn’t have to spend additional time to reformat data manually. Google sheets introduce and maintain over 200 google sheets formulas and function to reduce rewrap time and work with google sheets without any hassle.

You are getting confused with google sheets, how and which area should need to cover, and how to become an expert in google sheets. This tutorial will cover what are the most useful google sheets formulas and how to implement them with examples.

Google sheets formulas must start with an equals sign (=) and the function need to define inside the curly bracket.Sponsored Links

 

Google Sheets pre-defined formulas

In the previous google sheets version to implement query to execute the formula. But now, google sheets become smart, and most of the query formulas covert and pre-defined google sheets formulas. It is very helpful to reduce your study & manual query time and reuse the formulas without any hassle.

The most important thing is when you enter custom google sheets formulas into the cell ( you may need to Double-click on the cell you want to enter the google formulas), it popup & guide you on how may need to fit in depending on your data.

Hey, I’m Never use Google Sheet Before

If you never use google sheets before, we highly recommend following these How to use Google Sheets guidelines which offer by the google Sheets team. it will contain how to create google sheet, how to share google sheets with other people you want any most stater guide kit.

 

#1. SUM() formula in google sheets

This is the most used formula in every spreadsheet software. sum formula uses for return sum of a series of cells. In google sheet have the following terms.

  • Your sum formula defines like this =SUM()
  • If you assign a single value into the SUM formula, will return the same value same as you applied.
  • If you supplied more than one value into the sum formula, make sure that each value separated by comma =SUM(value1, value2, value2, ….).

How to use SUM() formula in google sheets (Basic Example)?

  1. Double click cell which you want to add sum query and enter (without double quote) ” =SUM( )” .then you can see helper popup also will show on cell.
    Quick tips:  move the mouse pointer to the top right in the toolbar then click Function > SUM.Double click dell which you want to add sum formula in google sheets
  2. Then press Ctrl Key and select the cell which you want to supplier for SUM Formula.
  3. Now all cells automatically assigned to the SUM formula will separate by a comma. Ctrl Key and select the cell which you want to supplier for SUM Formula : Top 17 Google Sheets Formulas Every Beginner's Should Know
  4. If you need to assign cell ranges into sum formula.  Ctrl Key and select cell which you need to start and then press the Shift key and select last value in cell range. Then close bracket )Ctrl Key and select cell which you need to start and then press the Shift key and select last value in cell range : Top 17 Google Sheets Formulas Every Beginner's Should Know

 

#2. SUMIF() formula in google sheets

SUMIF() formula is something like a combination of sum & if formulas in google sheets. Its use Returns a conditional sum across a range.

  •  make sure that SUMIF only performs with a single criterion. There is no way to configure with multiple criteria. You need to replace DSUM instead of.
  •  if your cell range validates with text against, then make sure that criterion must be a string.
  • If your cell range validates with number against logical expression criterion will be applicable.
    Logical operator Operator Name Description
    = equal to Compare criterion defined value to another value in the range
    > greater than Check whether criterion defined value greater than the value in the range
    < Less than Check whether criterion defined value less than the value in the range
    >= greater than Check whether criterion defined value greater than or equal to the value in the range
    <= Less than Check whether criterion defined value less than or equal to the value in the range
    <> Not equal to Check whether criterion defined value not equal to the value in the range

weather: අවස්ථාව, කාලගුණ, කාලගුණය, කුණාටුවට හෝ අමාරුවට අසුවී අනතුරට නොපැමිණ බේරෙන, දේශගුණය

English – Sinhala

How to use SUMIF formula in google sheets (Basic Example)?

In the below example, we need to get the total mark of maths in every student in the classroom.

How to use sumif formula in google sheets

  1. Double click the cell which you want to show the total math mark, then insert the cell range against the criterion.
  2. Add criterion you want to execute against a range 
  3. Add range which needs to sum.

 

#3. COUNT() formula in google sheets

COUNT() formula in google sheet all to return the count of the number (number of numeric) is specifically defined in named range. you may need to consider the following terms when you are dealing with COUNT formula,

  • COUNT formula only executes numeric values in the datasheet and text values are ignored.
  •  Also, COUNT formula executes all the numerical value which defined in the selected data range including numbers which appear more than once.
  • You can define cell value as Named Ranged or individual value which separate by comma )
COUNT(A2:A10,B2:B10,4,26)

COUNT(1,2,3,4,5,B6:B20)

 

#4. COUNTIF formula in  google sheets

This is also another most useful and important google sheet formula. countif formula uses to return conditional counts in range with a single criterion you may need to use COUNTIFS formula to return the count of a range depending on multiple criteria. Make sure that it only executes with a single criterion & no need to worry about case sensitivity. this formula mostly uses to highlight duplicate data in spreadsheets.

  1. The countif formula operates base on the range. if the range contains text against, the criterion must be a string.
  2. also if the range contains numbers to check against criterion, either number or string.
  3. all criterion need to define inside the double quote and any of the following operators: =, >, >=, <, or <=, use to check whether the range cell is equal to, greater than, greater than or equal to, less than, or less than or equal to the criterion value, respectively.

How to use COUNTIF formula in google sheets (Basic Example)?

In the above example, we created a simple spreadsheet which contains student mark in the classroom. We need to get,

  1. Number of marks count which got more than 75
  2. Number of marks count which got less than or equal to 55
  1. Double click the cell which you want to show the answer then type =COUNTIF( “.then you can see helper popup also will show on cell). 
  2. Then select the range you want to execute again countif criteria. You can also create dynamic named ranges using the google sheets function.
  3. Then insert your criterion operation to execute against the selected range.

 

#5. IF formula in google sheets

This is the one of powerful logical operation formulas in google sheets. IF formula can be used to check condition whether is OK or NOT in selected cell or range. it will return a boolean value if it’s TRUE or FALSE. you can setup up a value base on the return value in the logical expression. For example, you can check exam passed of failed students based on their subject mark average using if condition operation.

  • This operation can be expressed with string, numeric or boolean value in the datasheet.
  • IF Formula only operates with a single condition. It does not operate with AND or OR logical operation. You may need to use the IFS formula for multiple operations.

if formula in google sheets

 

#6. IFERROR Formula in google sheets

When you work with the formulas in google sheets. you may need to face diverse types of errors while executing it. This will happen when formula syntax does not fulfill the basic requirement. This will pain stakeholders because they willing to get meaningful results instead of unwanted errors.

So google sheets introduces a function called  IFERROR for preventing unwanted errors & replace the meaningful value on a cell in google sheets.

IFERROR formula in google sheet

This IFERROR formula can be used with any other function or formula which returns the value after execution.

 

#7. ISBLANK Formula in google sheets

ISBLANK formula can be used to validate cell value exit or not. this ISBLANK() formula will return TRUE if the value does not exist (empty) on the cell. either it will return as FALSE status.

ISBLANK(value)

=ISBLANK("hello")  // String value validation
=ISBLANK(3) // Integer value validation
=ISBLANK(A1)  // Cell reference assing
=ISBLANK(A1:A4) // Named Range assign

this function will mostly use condition operation formulas such as IF operation. Usually, that condition operation will break when assing empty cell or value in a given range.

Related posts:

  1. How to Make Multiple Selection in Drop-down Lists in Google Sheets
  2. 5 Easy Ways To Create A Pivot Table In Google Sheets (With Example)
  3. Conditional Formatting in Google Sheets ( 6 Useful Examples )
  4. How to add checkbox in google sheets ( 7 Useful Example )
  5. Right Way To Use Google Sheets IF Function (6 Helpful Tips)
  6. How To Format Date In Google Sheets : Simple Step

Related Posts

Scroll to Top