Excel Math 5: More Logic CountIf(), SumIf(), Ifs()

training@health.ufl.edu

Updated: 05/27/2021

Excel Math 5: More Logic – CountIf(), SumIf(), Ifs() 1.0 hour

Logic Test (True/False functions) ........................................................................................ 1

Combining Logic Functions ................................................................................................. 1

AND ................................................................................................................................. 1 OR .................................................................................................................................... 1 NOT ................................................................................................................................. 1

IS Worksheet Functions ...................................................................................................... 2

IF Error Worksheet Functions ............................................................................................. 2

IFNA ................................................................................................................................. 2 IFERROR ........................................................................................................................... 2

IF Worksheet Function ........................................................................................................ 3

IF ...................................................................................................................................... 3 IFS .................................................................................................................................... 3

Conditional Functions ......................................................................................................... 4

COUNTIF .......................................................................................................................... 4 SUMIF .............................................................................................................................. 4 SUMIFs ............................................................................................................................ 5 AVERAGEIF ...................................................................................................................... 5 Conditional Function Summary ...................................................................................... 6

SWITCH ................................................................................................................................ 6

Class Exercise - Grades ........................................................................................................ 7

Class Exercise – "Flags" ....................................................................................................... 8

Class Exercise – Summary Sheet ......................................................................................... 9

Class Exercise – Summary Sheet - PivotTable ................................................................... 11

Class Evaluation: https://ufl.qualtrics.com/jfe/form/SV_1Ojjkl6lRsKV3XT

Pandora Rose Cowart Education/Training Specialist UF Health IT Training C3-013 Communicore (352) 273-5051 PO Box 100152 prcowart@ufl.edu Gainesville, FL 32610-0152 http://training.health.ufl.edu

https://ufl.qualtrics.com/jfe/form/SV_1Ojjkl6lRsKV3XTmailto:prcowart@ufl.eduhttp://training.health.ufl.edu/

1

Logic Test (True/False functions) Most of Microsoft Excel's logic functions will return a TRUE or FALSE, but simple True or False values can be found with comparisons using the equal, greater than, and less than symbols

Equal = 5=5 TRUE 5=10 FALSE

Not Equal 55 FALSE 510 TRUE

Greater Than > 5>5 FALSE 5>10 FALSE

Greater Than or Equal >= 5>=5 TRUE 5>=10 FALSE

Less Than < 5

2

IS Worksheet Functions This set of functions return TRUE or FALSE based if the match the following conditions:

FUNCTION RETURNS TRUE IF ISBLANK Value refers to an empty cell. ISERR Value refers to any error value except #N/A. ISERROR Value refers to any error value (#N/A, #VALUE!, #REF!,

#DIV/0!, #NUM!, #NAME?, or #NULL!). ISLOGICAL Value refers to a logical value. ISNA Value refers to the #N/A (value not available) error value. ISNONTEXT Value refers to any item that is not text. (Note that this

function returns TRUE if the value refers to a blank cell.) ISNUMBER Value refers to a number. ISREF Value refers to a reference. ISTEXT Value refers to text.

A1=5 A2=Div/0

=ISBLANK(A1) FALSE =ISBLANK(A2) FALSE =ISERR(A1) FALSE =ISERR(A2) TRUE =ISERROR(A1) FALSE =ISERROR(A2) TRUE =ISLOGICAL(A1) FALSE =ISLOGICAL(A2) FALSE =ISNA(A1) FALSE =ISNA(A2) FALSE =ISNONTEXT(A1) TRUE =ISNONTEXT(A2) TRUE =ISNUMBER(A1) TRUE =ISNUMBER(A2) FALSE =ISREF(A1) TRUE =ISREF(A2) TRUE =ISTEXT(A1) FALSE =ISTEXT(A2) FALSE

IF Error Worksheet Functions Newer versions of Microsoft Excel offer IFNA and IFERROR to simplify our equations. IFNA Returns the value you specify if a formula returns the #N/A error value; otherwise it returns the result of the formula.

IFNA(value, value_if_na) The IFNA function syntax has the following arguments.

IFERROR Returns the value you specify if a formula returns a #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!. Error value; otherwise it returns the result of the formula.

IFERROR(value, value_if_error) The IFERROR function syntax has the following arguments:

3

IF Worksheet Function The TRUE or FALSE can be used as the "logic_test" in our conditional statements. We can vary the answer depending on the logic test result. IF Specifies a logical test to perform

Syntax: IF(logical_test, value_if_true, value_if_false)

When the value_if_true and value_if_false arguments are evaluated, IF returns the value returned by those statements.

Examples: =IF(A10C2, "Over Budget", IF(B2+50>C2,"Getting close", "OK"))

It often helps to draw a Logic Tree A10C2

Over Budget OK SUM(B5:B15) "" Over Budget B2+50 > C2 Getting Close OK IFS Specifies multiple logical tests to perform

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.

Syntax: IF(logical_test1, value_if_true1, logical_test2, value_if_true2…) Compare the functions to see which you feel is easier to read:

=IFS(A2>89, "A", A2>79, "B", A2>69, "C", A2>59, "D", TRUE, "F")

=IF(A2>89, "A", IF(A2>79, "B", IF(A2>69, "C", IF(A2>59, "D", "F"))))

=IFS(B2>C2, "Over Budget", B2+50>C2,"Getting close", TRUE, "OK")

=IF(B2>C2, "Over Budget", IF(B2+50>C2,"Getting close", "OK")

4

Conditional Functions COUNTIF Counts the number of nonblank cells that meet the given criteria

Syntax: COUNTIF(range, criteria)

Range is the range of cells from which you want to count cells. Criteria is the criteria in the form of a number, expression, or text that

defines which cells will be counted. For example, criteria can be expressed as 32, "32", ">32", "apples".

=COUNTIF(A1:A4, "apples") 2

=COUNTIF(B1:B4, ">55") 2

COUNTIFS Counts the number of nonblank cells across multiple ranges and counts the number of times all criteria are met. Search Range, Count if matches Criteria + Search Range, Count if matches Criteria + …

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

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. You can use the wildcard characters— the question mark (?) and asterisk (*) — in criteria. A question mark matches any single character, and an asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) before the character.

SUMIF Adds the cells specified by a given criteria

Syntax: SUMIF(range, criteria, sum_range) Range is the range of cells you want evaluated. Criteria is the criteria in the form of a number, expression, or

text that defines which cells will be added. For example, criteria can be expressed as 32, "32", ">32", "apples".

Sum_range are the actual cells to sum. The cells in sum_range are summed only if their corresponding cells in range match the criteria. If sum_range is omitted, the cells in range are summed.

=SUMIF(A2:A5, ">250000", B2:B5) $49, 000

=SUMIF(A2:A5, "

5

SUMIFs Adds the cells specified by a given criteria (Multiple)

Syntax: SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

Sum_range - (required) The range of cells to sum. Criteria_range1 - (required) The range that is tested using Criteria1. Criteria_range1 and Criteria1 set up a search pair whereby a range is searched for specific

criteria. Once items in the range are found, their corresponding values in Sum_range are added.

Criteria1 - (required) The criteria that defines which cells in Criteria_range1 will be added. For example, criteria can be entered as 32, ">32", B4, "apples", or "32".

Criteria_range2, criteria2, … (optional) Additional ranges and their associated criteria. You can enter up to 127 range/criteria pairs.

AVERAGEIF Returns the average (arithmetic mean) of all the cells in a range that meet a given criteria.

Syntax: AVERAGEIF(range, criteria, [average_range]) Range - Required. One or more cells to average, including numbers or names, arrays, or

references that contain numbers. Criteria - Required. The criteria in the form of a number, expression, cell reference, or text that

defines which cells are averaged. For example, criteria can be expressed as 32, "32", ">32", "apples", or B4.

Average_range - Optional. The actual set of cells to average. If omitted, range is used. AVERAGEIFS Returns the average (arithmetic mean) of all cells that meet multiple criteria.

Syntax: AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...) Average_range Required. One or more cells to average, including numbers or names, arrays,

or references that contain numbers. Criteria_range1, criteria_range2, … Criteria_range1 is required, subsequent criteria_ranges

are optional. 1 to 127 ranges in which to evaluate the associated criteria. Criteria1, criteria2, ... Criteria1 is required, subsequent criteria are optional. 1 to 127 criteria

in the form of a number, expression, cell reference, or text that define which cells will be averaged. For example, criteria can be expressed as 32, "32", ">32", "apples", or B4.

6

Conditional Function Summary

=CountIF( Range, Criteria) CountIf counts all the values in the selection (range) that match your logic test (criteria)

=CountIFs(Range, Critera, Range, Criteria, …) CountIfs is equivalent the sum of multiple CountIF statements.

=SumIF( SearchRange, Criteria, SumRange) SumIf adds all the values in the SumRange, when your SearchRange matches your logic test (criteria)

=SumIF( SumRange, SearchRange, Criteria, SearchRange, Criteria, …) SumIfs adds all the values in the SumRange when the criteria matches each SearchRange.

=AverageIf(range, criteria, [average_range]) AverageIf averages all the values in the Average_range, when your SearchRange matches your logic test

=AverageIF( AverageRange, SearchRange, Criteria, SearchRange, Criteria, …) AverageIfs adds all the values in the AverageRange when the criteria matches each SearchRange.

SWITCH The SWITCH function evaluates one value (called the expression) against a list of values, and returns the result corresponding to the first matching value. You may choose this over an IF or vLookup statement when the values are an exact match. SWITCH only performs an exact match, so you can't include logical operators like greater than (>) or less than (

7

Class Exercise - Grades 1. Grade Pass/Fail

=IF(A2>=70,"Pass","Fail")

2. Grade Pass/Fail/Withdraw =IF(A2="W", "Withdrawn", IF(A2>=70,"Pass","Fail"))

3. Letter Grade Nested IF =IF(A2>=90,"A",IF(A2>=80,"B",IF(A2>=70,"C",IF(A2>=60,"D","F"))))

A B

1 Grades Letter Grade 2 71 3 90

4 66

5 75

6 82

7 81

8 53

9 79

10 94

If(Grade >= 70 ) Pass Fail

8

Class Exercise – "Flags" You may have heard the word "flagged" in a negative context, usually in the context of a Red Flag warning. But this

1. Nested IF =IF(E:E="Gainesville",IF(I:I>2000,"x",""),"")

=IF(H:H="Alachua",if(E:E"Gainesville","x",""),"") =IF(H:H="Bradford","x",IF(I:I>2300,"x","")

2. AND Logic =IF(AND(H:H="Alachua",E:E"Gainesville"),"x","")

3. OR Logic =IF(OR(H:H="Bradford",I:I>2300),"x","")

9

Class Exercise – Summary Sheet

A B C D E F G 1 Date Fiscal Year Quarter Acct# County Sale Sales Person 2 7/1/2018 FY 18-19 1st Qtr 362-549 Alachua $48 Ann 3 7/1/2018 FY 18-19 1st Qtr 569-214 Bradford $48 Dan 4 7/1/2018 FY 18-19 1st Qtr 509-809 Alachua $46 Bob 5 7/1/2018 FY 18-19 1st Qtr 935-093 Alachua $40 Dan 6 7/1/2018 FY 18-19 1st Qtr 922-726 Alachua $34 Dan 7 7/1/2018 FY 18-19 1st Qtr 116-814 Alachua $31 Ann 8 7/1/2018 FY 18-19 1st Qtr 509-809 Alachua $27 Bob 9 7/1/2018 FY 18-19 1st Qtr 658-578 Alachua $25 Bob

1. Sum all of the Sales

Add all the values in column Sale

=SUM(F:F)

2. Sum of Sales by Sales Person If the Sales Person matches the value in cell I4, sum its values in Sale

J4: =SUMIF(G:G, I4,F:F)

3. Sum of Sales by County If the County matches the value in cell L4, sum its values in Sale

M4: =SUMIF(E:E,L4,F:F)

4. Sum of Sales by Sales Person and County

Sum all the Sales when – Sales Person matches the value in cell I11 AND County matches the value in J10

SUMIFS($F:$F, $G:$G, $I11, $E:$E, J$10)

Because we are now filling/copying the formula across, we need to think about the Absolute vs Relative references, which values are "Forced", where do we put the dollar signs? $F:$F - The County column must stay in place as we fill the equation across, lock all. $G:$G - The Sales Person column must stay in place as we fill the equation across, lock all. $I11 - The Sales Person match must stay in Column I as we move across but the Row number

can change. Lock the column, not the row. $E:$E - The Sale column must stay in place as we fill the equation across, lock all. J$10 - The County match must stay in Row 10 as we move down but the column number can

change. Lock the row, not the column.

I J 3 Sales Person Total Sales 4 Ann $39,407 5 Dan $38,840

L M 3 County Total Sales 4 Alachua $109,891 5 Bradford $6,799

I J 10 Sales Person Alachua 11 Ann $28,531 12 Dan $28,315

10

A B C D E F G 1 Date Fiscal Year Quarter Acct# County Sale Sales Person 2 7/1/2018 FY 18-19 1st Qtr 362-549 Alachua $48 Ann 3 7/1/2018 FY 18-19 1st Qtr 569-214 Bradford $48 Dan 4 7/1/2018 FY 18-19 1st Qtr 509-809 Alachua $46 Bob 5 7/1/2018 FY 18-19 1st Qtr 935-093 Alachua $40 Dan 6 7/1/2018 FY 18-19 1st Qtr 922-726 Alachua $34 Dan 7 7/1/2018 FY 18-19 1st Qtr 116-814 Alachua $31 Ann 8 7/1/2018 FY 18-19 1st Qtr 509-809 Alachua $27 Bob 9 7/1/2018 FY 18-19 1st Qtr 658-578 Alachua $25 Bob

5. Sum of Sales by Sales Person, Quarter, and Fiscal Year 18-19

Sum all the Sales when – Sales Person matches the value in cell I19 AND Quarter matches the value in J18 AND Fiscal Year matches the value in I17

SUMIFS($F:$F, $G:$G, $I19, $E:$E, J$18, $B:$B, $I$17) Where do we put the dollar signs? $F:$F - The Sale column must stay in place as we fill the equation across, lock all. $G:$G - The Sales Person column must stay in place as we fill the equation across, lock all. $I19 - The Sales Person match must stay in Column I as we move across but the Row number

can change. Lock the column, not the row. $E:$E - The Quarter column must stay in place as we fill the equation across, lock all. J$18 - The Quarter match must stay in Row 18 as we move down but the column number can

change. Lock the row, not the column.

$E:$E - The Fiscal Year column must stay in place as we fill the equation across, lock all. $I$17 - The Fiscal Year match will stay the same, lock all.

6. Sum of Sales by Sales Person, Quarter, and Fiscal Year 19-20 Sum all the Sales when – Sales Person matches the value in cell I27

AND Quarter matches the value in J26 AND Fiscal Year matches the value in I25

SUMIFS($F:$F, $G:$G, $I27, $E:$E, J$26, $B:$B, $I$25)

I J 17 FY 18-19 18 Sales Person 1st Qtr 19 Ann $5,550 20 Dan $5,184

I J 25 FY 19-20 26 Sales Person 1st Qtr 27 Ann $4,319 28 Dan $4,984

11

Class Exercise – Summary Sheet - PivotTable You may prefer to use a PivotTable to let Excel do the math.

• Put yourself in Cell A1 • From the Insert Tab, choose PivotTable • Click OK

1. Sum all of the Sales

Click on the check box for Sale, and Excel will show you the Sum of the sales.

2. Sum of Sales by Sales Person Click the check box for Sales Person and Excel will add the labels to the Row headings.

3. Sum of Sales by County

Uncheck the Sales Person, and check the County.

4. Sum of Sales by Sales Person and County Move County to the Column section, and click the check box for Sales Person again.

5. Sum of Sales by Sales Person, Quarter, and Fiscal Year 18-19 Uncheck the County, and move the Quarter to the Column Group Add Fiscal Year to the Filter Group Change the filter to FY 18-19

6. Sum of Sales by Sales Person, Quarter, and Fiscal Year 19-20

Change the filter to FY 19-20

Logic Test (True/False functions)Combining Logic FunctionsANDORNOT

IS Worksheet FunctionsIF Error Worksheet FunctionsIFNAIFERROR

IF Worksheet FunctionIFIFS

Conditional FunctionsCOUNTIFCOUNTIFS

SUMIFSUMIFsAVERAGEIFAVERAGEIFS

Conditional Function Summary

SWITCHClass Exercise - GradesClass Exercise – "Flags"Class Exercise – Summary SheetClass Exercise – Summary Sheet - PivotTable