COUNT Using Multiple Criteria in Microsoft Excel


Excel

COUNTIF provides for one criteria. If we count column A, where vehicle type is Cars:

=COUNTIF(A2:A10,”Cars”)

But what if we want to count of records where vehicle type is cars and the color is black? We cannot use the COUNTIF function for multiple criteria. We need to use an array formula, which requires that we press Ctrl+Shift+Enter after typing it, instead of just Enter.

=SUM((A2:A10=”Cars”)*(B2:B10=”Black”))

Count of records with between 500 and 800 units sold. This also requires an array formula, press Ctrl+Shift+Enter after typing it.

=SUM((C2:C10>=500)*(C2:C10<=800))

Because these formulas are Array Formulas (sometimes referred to as CSE formulas because we must Ctrl+Shift+Enter to enter them), they look a little different. In the formula bar, after it's been properly entered, the above formula looks like this.

{=SUM((C2:C10>=500)*(C2:C10<=800)) }