» Counting Based on Multiple Criteria
CATEGORY - Excel Array Formulas
VERSION - All Microsoft Excel Versions
To count the number of rows that must satisfy criteria from two columns:
Insert the following Array Formula (see how to insert an Array Formula in page 2 of this Tip):
{=SUM((Market="USA")*(Customer_Name="ExcelTip"))}
The result of the calculation is 2. The * symbol in the Array Formula returns a result equal to the AND operator.
To count the number of cells that satisfy at least one of two criteria from different ranges:
Replace the * symbol in the Array Formula with a + sign.
The return result calculation is 5, that is, five cells in column B match the criteria ExcelTip and four cells in column C match the criteria USA.
Note: This can also be done by replacing the Array Formula with a COUNTIF formula:
=COUNTIF (Market, "USA")+ COUNTIF(Customer_Name, "ExcelTip")
Using keyboard shortcuts
The keyboard shortcut for inserting an Array Formula is as follows (see Tip #201):
After entering the formula into the cell, select the cell, press F2 to edit it, and then press Ctrl+Shift+Enter.
Book Store:
how can this work
rslotpole
interesting but how can this array formula work? It never references the cells it is counting over. How are cells b2-b11 and c2-c11 being referenced?
how can this work
rslotpole
interesting but how can this array formula work? It never references the cells it is counting over. How are cells b2-b11 and c2-c11 being referenced?
re: how can this work
EmilyTheChef
the columns have range names (the range Name for column B is Customer_Name, and for column C it is Market).
how can this work
rslotpole
interesting but how can this array formula work? It never references the cells it is counting over. How are cells b2-b11 and c2-c11 being referenced?
An other way
Ali
Hi,
There is another way to get the same result for counting based on multiple criteria .. use the following formula:
=SUMPRODUCT((Market="USA")*(Customer_Name="ExcelTip"))
I hope that will be useful for everybody,
And, what about using a wildcard in the criteria
Mindy
For my example, I want to calculate all Entertainment expenses (ie, Entertainment - Movies, Entertainment - Eating Out, Entertainment - Fun, etc.) that accrued on 03/07/07.
=SUM(IF((DAY(A2:A15)=7)*(B2:B15="Entertainment*"),E2:E15))
How can you use a wildcard in this formula?
