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 bestselling book, Learn Excel from MrExcel. You can sign up to receive chapters from this book every Tuesday for free.
By Bill Jelen on 05Jul2002
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.
