If functions

Question 1

if

Answer 1

allows you to make logical comparisons between a value and what you expect.

For example, =IF(C2=”Yes”,1,2) says IF(C2 = Yes, then return a 1, otherwise return a 2).

Question 2

ifs

Answer 2

The IFS function checks whether one or more conditions are met, and returns a value that corresponds to the first TRUE condition. IFS can take the place of multiple nested IF statements, and is much easier to read with multiple conditions.

=IFS([Something is True1, Value if True1,Something is True2,Value if True2,Something is True3,Value if True3)

Question 3

10

iferror

Answer 3

IFERROR(value, value_if_error)

The IFERROR function syntax has the following arguments:

value Required. The argument that is checked for an error.

value_if_error Required. The value to return if the formula evaluates to an error. The following error types are evaluated: #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!.

IFERROR returns a value you specify if a formula evaluates to an error; otherwise, it returns the result of the formula.

Question 4

sumif

Answer 4

SUMIF(range, criteria, [sum_range])

range Required. The range of cells that you want evaluated by criteria. Cells in each range must be numbers or names, arrays, or references that contain numbers. Blank and text values are ignored. The selected range may contain dates in standard Excel format (examples below).

criteria Required. The criteria in the form of a number, expression, a cell reference, text, or a function that defines which cells will be added. Wildcard characters can be included - a question mark (?) to match any single character, an asterisk (*) to match any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) preceding the character.

Question 5

4

sumifs

Answer 5

same as sumif but with multiple criteria

SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)

if you want to sum cells that have the same root but different variations (amazon eu, amazon US, amazon, etc.), use the a cell reference with “amazon” in it and then add “*” to indicate that no matter what comes after, it gets added in

Question 6

nested ifs

Answer 6

AND – =IF(AND(Something is True, Something else is True), Value if True, Value if False)

OR – =IF(OR(Something is True, Something else is True), Value if True, Value if False)

NOT – =IF(NOT(Something is True), Value if True, Value if False)

Question 7

countif

Answer 7

=COUNTIF(Where do you want to look?, What do you want to look for?)

COUNTIF(range, criteria)

Question 8

countifs

Answer 8

same thing is countif but with multiple criteria

COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)

The COUNTIFS function syntax has the following arguments:

criteria_range1 Required. The first range in which to evaluate the associated criteria.

criteria1 Required. The criteria in the form of a number, expression, cell reference, or text that define which cells will be counted. For example, criteria can be expressed as 32, “>32”, B4, “apples”, or “32”.

criteria_range2, criteria2, … Optional. Additional ranges and their associated criteria. Up to 127 range/criteria pairs are allowed.