MrExcel Publishing
Your One Stop for Excel Tips & Solutions

CountIf or SumIf in Excel with Two Conditions


July 05, 2002 - by Bill Jelen

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.

I wrote a related article a few years ago, a better version of the formula has come to light. It 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.


Bill Jelen is the author / co-author of
Excel Dynamic Arrays Straight to the Point

The new Dynamic Array Functions are just one side-effect of an effort to completely rewrite the Calculation Engine in Excel. Joe McDaid and the rest of the CALC team have the laid the groundwork for all future functions in Excel. Yes, the first crop of SORT, SORTBY, FILTER, UNIQUE, SEQUENCE and RANDARRAY are awesome and powerful, but they are just the first of many new functions that will come to Office 365 over the coming years.