[et_pb_section fb_built=”1″ _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content” da_is_popup=”off” da_exit_intent=”off” da_has_close=”on” da_alt_close=”off” da_dark_close=”off” da_not_modal=”on” da_is_singular=”off” da_with_loader=”off” da_has_shadow=”on” da_disable_devices=”off|off|off”][et_pb_row _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][et_pb_column type=”4_4″ _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][et_pb_text _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Google Sheet IF functions allow you to include condition-based formulas in your spreadsheets.\u00a0<\/p>\n
Think of them like flow charts. These charts have a set of questions at each juncture and your answer defines how you move forward. Likewise, IF functions return one result or another based on a certain condition.<\/p>\n
If you are looking to move past basic formulas these are great functions to have in your toolbox. There are quite a few of them and in this article we will cover them all.<\/p>\n
[\/et_pb_text][et_pb_text module_id=”if” _builder_version=”4.27.4″ _module_preset=”default” hover_enabled=”0″ global_colors_info=”{}” theme_builder_area=”post_content” sticky_enabled=”0″]<\/p>\n
This is the most basic function in this category. Basically, it evaluates a single condition and outputs different results if it\u2019s TRUE or FALSE.[\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
=IF(condition, value_if_true, value_if_false)<\/p>\n
[\/et_pb_text][et_pb_text module_id=”if” _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
IF is great to make simple logical comparisons. For example, you can evaluate whether students “Pass” a test if their score is over 50.<\/p>\n
Note: given the formula below students who got exactly 50 will also be assigned a \u201cFail\u201d.<\/p>\n
[\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
=IF(A1 > 50, “Pass”, “Fail”)<\/p>\n
[\/et_pb_text][et_pb_text module_id=”if” _builder_version=”4.24.1″ _module_preset=”default” locked=”off” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Read our post on IF<\/a> for advanced uses like nested IF statements and to combine it with other functions.<\/p>\n [\/et_pb_text][et_pb_text module_id=”if” _builder_version=”4.27.4″ _module_preset=”default” hover_enabled=”0″ locked=”off” global_colors_info=”{}” theme_builder_area=”post_content” sticky_enabled=”0″]<\/p>\n This function has a number of conditions and values. The first condition to be TRUE will return its corresponding value.[\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.24.1″ _module_preset=”default” locked=”off” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n =IFS(condition1, value1, condition2, value2, …)<\/p>\n [\/et_pb_text][et_pb_text module_id=”if” _builder_version=”4.24.1″ _module_preset=”default” locked=”off” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n The IFS function is useful to evaluate multiple conditions against a set of values. For example, we could use it to translate grades from percentages to letters.<\/p>\n [\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.24.1″ _module_preset=”default” locked=”off” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n =IFS(A1 > 90, “A”, A1 > 80, “B”, A1 > 70, “C”, A1 > 60, “D”, TRUE, “F”)<\/p>\n [\/et_pb_text][et_pb_text module_id=”if” _builder_version=”4.24.1″ _module_preset=”default” locked=”off” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n For a more detailed overview, check out the full article on the IFS formula in Google Sheets<\/a>.<\/p>\n [\/et_pb_text][et_pb_text module_id=”if” _builder_version=”4.27.4″ _module_preset=”default” hover_enabled=”0″ locked=”off” global_colors_info=”{}” theme_builder_area=”post_content” sticky_enabled=”0″]<\/p>\n Calculates the average of a range of cells that meet a specified condition.[\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.24.1″ _module_preset=”default” locked=”off” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n =AVERAGEIF(range, criterion, [average_range])<\/p>\n [\/et_pb_text][et_pb_text module_id=”if” _builder_version=”4.24.1″ _module_preset=”default” locked=”off” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n The AVERAGEIF function is useful to limit the values you want to average. Continuing with the test example, we could use it to find the average test score for students who passed.<\/p>\n [\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.24.1″ _module_preset=”default” locked=”off” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n =AVERAGEIF(A1:A10, \u201c> 50\u201d)<\/p>\n [\/et_pb_text][et_pb_text module_id=”if” _builder_version=”4.24.1″ _module_preset=”default” locked=”off” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n For a detailed overview of its syntax, check out the full article on the AVERAGEIF formula in Google Sheets<\/a>.<\/p>\n [\/et_pb_text][et_pb_text module_id=”if” _builder_version=”4.27.4″ _module_preset=”default” hover_enabled=”0″ locked=”off” global_colors_info=”{}” theme_builder_area=”post_content” sticky_enabled=”0″]<\/p>\n Calculates the average of a range of cells that meet multiple specified conditions.[\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.24.1″ _module_preset=”default” locked=”off” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n =AVERAGEIFS(average_range, criteria_range1, criterion1, criteria_range2, criterion2, …)<\/p>\n [\/et_pb_text][et_pb_text module_id=”if” _builder_version=”4.24.1″ _module_preset=”default” locked=”off” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n AVERAGEIFS lets you set multiple conditions to narrow down the values to include in your average. For example, you could average the test scores for students who passed and are right handed.<\/p>\n [\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.24.1″ _module_preset=”default” locked=”off” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n =AVERAGEIFS(B1:B10, A1:A10, “Pass”, C1:C10, \u201cRight\u201d)<\/p>\n [\/et_pb_text][et_pb_text module_id=”if” _builder_version=”4.24.1″ _module_preset=”default” locked=”off” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n For more an in-depth explanation, read the complete post on AVERAGEIFS formula in Google Sheets<\/a>.<\/p>\n [\/et_pb_text][et_pb_text module_id=”if” _builder_version=”4.27.4″ _module_preset=”default” hover_enabled=”0″ locked=”off” global_colors_info=”{}” theme_builder_area=”post_content” sticky_enabled=”0″]<\/p>\n Counts the number of cells in a range that meet a specified condition.[\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.24.1″ _module_preset=”default” locked=”off” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n =COUNTIF(range, criterion)<\/p>\n [\/et_pb_text][et_pb_text module_id=”if” _builder_version=”4.24.1″ _module_preset=”default” locked=”off” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n This function is great to know how many students passed the test.<\/p>\n [\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.24.1″ _module_preset=”default” locked=”off” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n =COUNTIF(A1:A10, “>50”)<\/p>\n [\/et_pb_text][et_pb_text module_id=”if” _builder_version=”4.24.1″ _module_preset=”default” locked=”off” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n For many applications of this formula, check out Countif greater than 0 Google Sheets<\/a>.<\/p>\n [\/et_pb_text][et_pb_text module_id=”if” _builder_version=”4.27.4″ _module_preset=”default” hover_enabled=”0″ locked=”off” global_colors_info=”{}” theme_builder_area=”post_content” sticky_enabled=”0″]<\/p>\n Counts the number of cells in a range that meet multiple specified conditions.[\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.24.1″ _module_preset=”default” locked=”off” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n =COUNTIFS(criteria_range1, criterion1, criteria_range2, criterion2, …)<\/p>\n [\/et_pb_text][et_pb_text module_id=”if” _builder_version=”4.24.1″ _module_preset=”default” locked=”off” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Again, say you are fixated on your right handed students. COUNTIFS will let you know how many of them passed the test.<\/p>\n [\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.24.1″ _module_preset=”default” locked=”off” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n =COUNTIFS(B55:B65, “> 50”<\/span>, C55:C65, “Right”<\/span>)<\/p>\n [\/et_pb_text][et_pb_text module_id=”if” _builder_version=”4.24.1″ _module_preset=”default” locked=”off” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n For more detailed information, check out the full article on the COUNTIFS formula in Google Sheets<\/a>.<\/p>\n [\/et_pb_text][et_pb_text module_id=”if” _builder_version=”4.27.4″ _module_preset=”default” hover_enabled=”0″ locked=”off” global_colors_info=”{}” theme_builder_area=”post_content” sticky_enabled=”0″]<\/p>\n Adds certain values based on a single condition.[\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.24.1″ _module_preset=”default” locked=”off” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n =SUMIF(range, criterion, [sum_range])<\/p>\n [\/et_pb_text][et_pb_text module_id=”if” _builder_version=”4.24.1″ _module_preset=”default” locked=”off” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n The SUMIF function is useful to single out certain values you want to add. It can calculate total sales for a\u00a0 product, sum expenses for a category, or add scores above a threshold.<\/p>\n [\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.24.1″ _module_preset=”default” locked=”off” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n =SUMIF(A1:A10, “Product A”, B1:B10)<\/p>\n [\/et_pb_text][et_pb_text module_id=”if” _builder_version=”4.24.1″ _module_preset=”default” locked=”off” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n For more practical examples, and advanced use cases, check out the full article on the\u00a0SUMIF formula in Google Sheets<\/a>.<\/p>\n [\/et_pb_text][et_pb_text module_id=”if” _builder_version=”4.27.4″ _module_preset=”default” hover_enabled=”0″ locked=”off” global_colors_info=”{}” theme_builder_area=”post_content” sticky_enabled=”0″]<\/p>\n Adds the cells in a range that meet multiple conditions.[\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.24.1″ _module_preset=”default” locked=”off” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n =SUMIFS(sum_range, criteria_range1, criterion1, criteria_range2, criterion2, …)<\/p>\n [\/et_pb_text][et_pb_text module_id=”if” _builder_version=”4.24.1″ _module_preset=”default” locked=”off” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n The SUMIFS function is perfect for adding up values that meet multiple criteria, such as summing sales only for a specific region and product.<\/p>\n [\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.24.1″ _module_preset=”default” locked=”off” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n =SUMIFS(B1:B10, A1:A10, “North”, C1:C10, “Product A”)<\/p>\n [\/et_pb_text][et_pb_text module_id=”if” _builder_version=”4.24.1″ _module_preset=”default” locked=”off” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n For more detailed information, check out the full article on the\u00a0SUMIFS formula in Google Sheets<\/a>.<\/p>\n [\/et_pb_text][et_pb_text module_id=”if” _builder_version=”4.27.4″ _module_preset=”default” hover_enabled=”0″ locked=”off” global_colors_info=”{}” theme_builder_area=”post_content” sticky_enabled=”0″]<\/p>\n Tests an expression against a list of cases and returns the corresponding value of the first match. It\u2019s a great alternative to nested IF statements because of its increased simplicity and readability.[\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.24.1″ _module_preset=”default” locked=”off” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n =SWITCH(expression, case1, value1, case2, value2, …, [default])<\/p>\n [\/et_pb_text][et_pb_text module_id=”if” _builder_version=”4.24.1″ _module_preset=”default” locked=”off” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n The SWITCH function is useful when you have multiple conditions to evaluate and want to return a specific value for the first true condition.<\/p>\n [\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.24.1″ _module_preset=”default” locked=”off” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n =SWITCH(A1, “Red”, 1, “Green”, 2, “Blue”, 3, “Unknown”)<\/p>\n [\/et_pb_text][et_pb_text module_id=”if” _builder_version=”4.24.1″ _module_preset=”default” locked=”off” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n To explore this function in full check out our post SWITCH formula in Google Sheets<\/a>.<\/p>\n [\/et_pb_text][et_pb_text module_id=”if” _builder_version=”4.27.4″ _module_preset=”default” hover_enabled=”0″ locked=”off” global_colors_info=”{}” theme_builder_area=”post_content” sticky_enabled=”0″]<\/p>\n Returns a value if there is no error, otherwise it returns a specified value if there is an error.[\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.24.1″ _module_preset=”default” locked=”off” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n =IFERROR(value, [value_if_error])<\/p>\n [\/et_pb_text][et_pb_text module_id=”if” _builder_version=”4.24.1″ _module_preset=”default” locked=”off” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n The IFERROR function is useful to handle errors in formulas. It helps ensure that your spreadsheet doesn’t show errors and displays user-friendly messages instead.<\/p>\n [\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.24.1″ _module_preset=”default” locked=”off” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n =IFERROR(100\/0, “Divide by zero error”)<\/p>\n [\/et_pb_text][et_pb_text module_id=”if” _builder_version=”4.24.1″ _module_preset=”default” locked=”off” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\nIFS<\/h2>\n
AVERAGEIF<\/h2>\n
AVERAGEIFS<\/h2>\n
COUNTIF<\/h2>\n
COUNTIFS<\/h2>\n
SUMIF<\/h2>\n
SUMIFS<\/h2>\n
SWITCH<\/h2>\n
IFERROR<\/h2>\n