All data is in therangeB5:B15.
Both approaches are explained below.
COUNTIFS function
TheCOUNTIFS functionreturns the count of cells that meet one or more criteria.
COUNTIFS can be used with criteria based on dates, numbers, text, and other conditions.
COUNTIFS supportslogical operators(>,<,<>,=) andwildcards(*,?)
Notice we have single range/criteria pair at this point.
To generate a count, all conditions must match.
To add more conditions, add more range/criteria pairs.
SUMPRODUCT alternative
you could also use theSUMPRODUCT functionto count rows that match multiple conditions.
the equivalent formula is:
This is an example of usingBoolean logicin a formula.
SUMPRODUCT is more powerful and flexible than COUNTIFS, which is in agroup of eight functions require ranges.
For more details, seeWhy SUMPRODUCT?.
Pivot table alternative
To summarize different combinations in a larger data set, consider aPivot Table.
Pivot tables are a fast and flexible reporting tool that can summarize data in many different ways.
For a direct comparison of SUMIF and Pivot tables, seethis video.
