About MrExcel
Consulting Services
Learn Excel Resources
Challenge of the Month
MrExcel Seminars

Message Board

MrExcel Store



Past Tip of the Day


Kári asks, I have a formula where I have to put in a criteria. The criteria is, that the formula has to gather numbers that are bigger than 0,5 (>0,5), but not bigger than 2 (<2). But how do I do that? I have tried: ">0,5"&"<2" and a lot of other combinations, but nothing works.

Kári: For a SUMIF or COUNTIF formula with 2 conditions, you need to use an array formula.
This type of formula is discussed here:

Since I wrote that article a few years ago, a better version of the formula has come to light. The web page discusses using this formula for a CountIf with 2 conditions:

You can use boolean logic instead to write this formula for CountIf
or this formula for SumIf:

Remember, you must hold down the Ctrl and Shift keys then hit enter to enter these CSE or Array formulas.

This tip, and 276 others are in the best-selling book, Learn Excel from MrExcel. You can sign up to receive chapters from this book every Tuesday for free.

By Bill Jelen on 05-Jul-2002 Consulting can be hired to implement this concept, or many other cool applications, with your data. provides examples of 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 Visual Basic procedures on this web site are provided "as is" and we do not guarantee that they can be used in all situations.


Excel is a registered trademark of the Microsoft® Corporation.

All contents Copyright 1998-2008 by MrExcel Consulting.