Beyond the Help files . . .

 

 

Access  ¨  Excel  ¨ FrontPage ¨ Outlook ¨ PowerPoint ¨  Word ¨    Miscellaneous  ¨  Tutorials  ¨  Windows  ¨  Free Downloads


 


 

COUNT Using Multiple Criteria in Microsoft 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)) }

 

Back to top...

 

 

OfficeArtilces.com debuted on May 26, 2005.

MrExcel.com provides examples of Formulas, Functions and Visual Basic procedures for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The Formulas, Functions and Visual Basic procedures on this web site are provided "as is" and we do not guarantee that they can be used in all situations.

Access®, Excel®, FrontPage®, Outlook®, PowerPoint®, Word® are registered trademarks of the Microsoft Corporation.
MrExcel® TM is a registered trademark of Tickling Keys, Inc.

All contents © 1998-2014 by MrExcel Consulting | All rights reserved