COUNTIF and COUNTIFS in Excel - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the COUNTIF function do in Excel?

COUNTIF counts the number of cells in a range that meet a single condition or criteria.

beginner

How is COUNTIFS different from COUNTIF?

COUNTIFS counts the number of cells that meet multiple conditions across one or more ranges, while COUNTIF only handles one condition.

beginner

Write the formula to count cells in range A1:A10 that are greater than 5.

The formula is =COUNTIF(A1:A10, ">5"). It counts cells with values greater than 5.

intermediate

Explain the syntax of COUNTIFS function.

COUNTIFS syntax: =COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...). You provide pairs of ranges and their conditions.

intermediate

Can COUNTIF and COUNTIFS handle text criteria? Give an example.

Yes. For example, =COUNTIF(B1:B10, "apple") counts cells exactly equal to "apple". You can also use wildcards like "app*" to count cells starting with "app".

What will =COUNTIF(A1:A5, ">10") do?

A Count cells with values less than 10 in A1:A5

B Count cells with values greater than 10 in A1:A5

C Count all cells in A1:A5

D Count cells equal to 10 in A1:A5

COUNTIF counts cells matching the condition. Here, it counts cells with values greater than 10.

Which function counts cells based on multiple conditions?

A COUNTIFS

B COUNTIF

C SUMIF

D AVERAGEIF

COUNTIFS allows multiple criteria, COUNTIF only one.

How would you count cells in B1:B10 that contain the text "cat" anywhere inside?

A =COUNTIF(B1:B10, "cat")

B =COUNTIF(B1:B10, "cat*")

C =COUNTIF(B1:B10, "?cat")

D =COUNTIF(B1:B10, "*cat*")

Using wildcards *cat* counts cells containing "cat" anywhere.

What is wrong with this formula? =COUNTIFS(A1:A5, ">5", B1:B10, "<3")

A COUNTIFS only accepts one range

B COUNTIFS cannot use > or < operators

C Ranges A1:A5 and B1:B10 must be the same size

D Criteria must be numbers, not strings

All criteria ranges in COUNTIFS must be the same size.

If you want to count cells in C1:C10 that are not empty, which formula is correct?

A =COUNTIF(C1:C10, "<>")

B =COUNTIF(C1:C10, "?*")

C =COUNTIF(C1:C10, "*")

D =COUNTIF(C1:C10, "")

"<>" means not equal to empty, so it counts non-empty cells.

Describe how to use COUNTIF to count cells with a single condition. Include an example.

Think about counting cells greater than a number or matching text.

Explain how COUNTIFS works with multiple conditions and why ranges must be the same size.

Consider how Excel checks each row across ranges.