← Excel from Scratch: Formulas, Functions, and Data Analysis
Lesson
AND/OR logic and conditional counting with COUNTIF
Combine several conditions with AND/OR and count the cells that meet a condition with COUNTIF.
AND, OR, and COUNTIF
Several conditions and counting by a condition
The AND function returns TRUE only when all the conditions are met at the same time. The OR function returns TRUE if at least one condition is true. Both functions are used inside IF to check several criteria.
An AND example: a discount is given only if the purchase amount is over 1,000 and the customer is a regular. Formula: =IF(AND(B2>1000, C2="Yes"), "10% off", "No discount"). Both conditions must be true at the same time.
An OR example: a bonus is paid if the manager met the sales volume target OR the number-of-deals target. Formula: =IF(OR(B2>500000, C2>20), "Bonus", "No bonus"). Meeting one condition is enough.
The COUNTIF function counts the cells in a range that meet a condition. Syntax: =COUNTIF(range, criteria). A criterion with text or a comparison operator goes in quotation marks: =COUNTIF(A1:A10, "Moscow") or =COUNTIF(B1:B10, ">100"). To match a number exactly, you can skip the quotes: =COUNTIF(C1:C10, 5).
Lesson notes
Several conditions and counting by a condition
The AND function returns TRUE only when all the conditions are met at the same time. The OR function returns TRUE if at least one condition is true. Both functions are used inside IF to check several criteria.
An AND example: a discount is given only if the purchase amount is over 1,000 and the customer is a regular. Formula: =IF(AND(B2>1000, C2="Yes"), "10% off", "No discount"). Both conditions must be true at the same time.
An OR example: a bonus is paid if the manager met the sales volume target OR the number-of-deals target. Formula: =IF(OR(B2>500000, C2>20), "Bonus", "No bonus"). Meeting one condition is enough.
The COUNTIF function counts the cells in a range that meet a condition. Syntax: =COUNTIF(range, criteria). A criterion with text or a comparison operator goes in quotation marks: =COUNTIF(A1:A10, "Moscow") or =COUNTIF(B1:B10, ">100"). To match a number exactly, you can skip the quotes: =COUNTIF(C1:C10, 5).