Back

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

Message Board

MrExcel Store
Podcast
Search
Contact
Home

 

 

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: http://www.mrexcel.com/tip031.shtml.

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:
=SUM(IF($C$2:$C$4403>0.5,IF($B$2:$B$4403<2,1,0),0))

You can use boolean logic instead to write this formula for CountIf
=SUM(($C$2:$C$4403>0.5)*($C$2:$C$4403<2)*1)
or this formula for SumIf:
=SUM(($C$2:$C$4403>0.5)*($C$2:$C$4403<2)*($C$2:$C$4403))

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

MrExcel.com Consulting can be hired to implement this concept, or many other cool applications, with your data.

MrExcel.com 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.