Counting Based on Multiple Criteria

My Account

Check out

Shopping Cart

JOIN OUR NEWSLETTER
and receive for joining:
	Free eBook Learn More!
	Free Excel ADD-IN
	Free Weekly Excel Tip








F1 eBook (Spanish)

» Counting Based on Multiple Criteria

CATEGORY: Excel Array Formulas

VERSIONS: All Microsoft Excel Versions

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.

Screenshot // Counting Based on Multiple Criteria

Rate this tip



1	2	3	4	5

RATING: 3.42
VIEWS: 60619

Email Tip

Print Tip

Add to My Tips

READER COMMENTS (view all comments)

how can this work
rslotpole wrote on December 31, 1969 19:00 EST

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 wrote on December 31, 1969 19:00 EST

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 wrote on December 31, 1969 19:00 EST

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 wrote on December 31, 1969 19:00 EST

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 wrote on December 31, 1969 19:00 EST

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 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))

How can you use a wildcard in this formula?

Microsoft PowerPoint Version 2002 Step by Step

Microsoft Access Version 2002 Step by Step

422 Tax Deductions for Businesses and Self-Employed Individuals : You Get a Raise Every Time You Find a Legitimate Tax Deduction

The Intelligent Investor: The Classic Bestseller on Value Investing

Microsoft Excel 2002 Visual Basic for Applications Step by Step

Definitive Guide to Excel VBA

RELATED MICROSOFT EXCEL TIPS

• Excel VBA books
• Accounting books
• Business Plans
• MS Office books
• Taxes books

VIEW ALL BOOKS

	Advertise With Us
Tips	Add-In in VBA \| Applications - Word, Outlook in VBA \| Array Formulas \| Cells, Ranges, Rows, and Columns in VBA \| Counting \| Custom Functions \| Custom Functions in VBA \| Database Formulas \| Database in VBA \| Date & Time Formulas \| Date & Time in VBA \| Events in VBA \| Excel 2003 \| Excel Chart \| Excel Consolidating \| Excel Counting \| Excel Custom Functions using VBA \| Excel Customizing \| Excel Data \| Excel Dates \| Excel Editing \| Excel Files \| Excel Filter \| Excel Format \| Excel Formula \| Excel General \| Excel Grouping and Outlining \| Excel Importing Text Files \| Excel Information \| Excel Keyboard Shortcuts \| Excel Loan Formulas \| Excel Macros - VBA \| Excel Pivot Tables \| Excel Printing \| Excel Range Name \| Excel Security - Protection \| Excel Sorting \| Excel Style \| Excel Subtotals \| Excel Summing \| Excel Text \| Excel Time \| Excel Tools \| Excel Worksheet, Workbook \| Files, Workbook, and Worksheets in VBA \| Financial Formulas \| Formating in VBA \| General Topics in VBA \| Import and Export in VBA \| Information Formulas \| Keyboard & Other Shortcuts in VBA \| Keyboard Formula Shortcuts \| Links between Worksheet and Workbooks \| Links in VBA \| Logical Formulas \| Lookup Formulas \| Mail - Send and Receive in VBA \| Menus, Toolbars, Status bar in VBA \| Modules, Class Modules in VBA \| Other Q&A Formulas \| Printing in VBA \| Protecting in VBA \| Summing \| Text Formulas \| User Forms, Input boxes in VBA \| Using Loops \| Working with Formulas \|
Tips by Version	Microsoft Excel 97 \| Microsoft Excel 2000 \| Microsoft Excel 2002 \| All Microsoft Excel Versions \| New in Excel 2002 \| New in Excel 2003 - Office 11
Website	Home \| Tip of Hour \| Recommended Tips \| Most Viewed Tips \| Tips by Version \| Submit a Tip \| My Tips Microsoft Excel Tutorials \| Excel Links \| Write for Us \| About Us \| Search Results \| Tip Archives \| Excel Forum \| Excel Forum Archives
Excel Book	Excel 97 Book \| Excel 2000 Book \| Excel 2002 Book \| Excel XP Book \| Book Store

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.
Copyright © 2003 ExcelTip.com Microsoft, Microsoft Excel is a U.S. registered trademark of Microsoft Corporation Site Developed By: Varien