Count IF and Conditional Formatting, Lecture notes of MS Microsoft Excel skills

Download Count IF and Conditional Formatting and more Lecture notes MS Microsoft Excel skills in PDF only on Docsity!

Count IF and Conditional Formatting

MICROSOFT EXCEL 2016 - INTERMEDIATE

Count IF() Function

Count IF() is a useful tool to count the number of cells that meet a criterion, for example, to count the number of times a particular item appears in a list.

The syntax has two arguments:

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

For example:

=COUNTIF(A1:E1501, "Approved")

“table range” “criteria”

If you wish to count multiple criteria, use COUNTIF S ()

EXERCISE ONE : Count IF() or Count IFS() -for multiple criteria

Count the number of “Approved”

Place the cursor in cell H1 and key in =CountIF(“1st argument”, “2 nd^ argument”)

1 st^ argument = Highlight the area of the table (A1 through E11) 2 nd^ argument = The criteria “Approved” (Note: it is not case-sensitive)

EXERCISE ONE : Count IF() or Count IFS() -for multiple criteria

Count approved dates between April 14 and 28; in G7, key in the following formula,

=COUNTIFS(B1:B1501,">4/13/2017", C1:C1501,"<4/29/2017", E1:E1501, "Approved")

Multiple items 1 s t^ range 1 s t^ criteria 2 nd^ range 2 nd^ criteria 3 rd^ range 3 rd^ criteria

Important: Don’t forget the quotation marks to identify strings of text and numbers

Sort & Filter

If you wish to sort the highlighted rule on the top (or bottom), for example, for “Vacation Cancelled”, in the Home ribbon, under “Editing”, click “Sort & Filter” and choose “Custom Sort”.

In the “Sort” dialogue box,

Sort by Leave Type Sort on Cell Color Order choose Yellow Place it On Top

Click OK