In this example, we will count the number of rows in which the text ExcelTip and USA appear in both columns B and C (the range Name for column B is Customer_Name, and for column C it is Market).
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.
And, what about using a wildcard in the criteria
Mindy wrote on December 31, 1969 19:00 EST
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))
Terms
and Conditions of use
The applications/code on this site are distributed as is and without warranties
or liability. In no event shall the owner of the copyrights, or the authors
of the applications/code be liable for any loss of profit, any problems
or any damage resulting from the use or evaluation of the applications/code.