1. Technology
You can opt-out at any time. Please refer to our privacy policy for contact information.

Excel SUM IF Array Formula Tutorial

Count Cells that Meet One or More Conditions with a an Array Formula


Using an Array Formula to Count

Using an Array Formula to Count

© Ted French

SUM IF Array Formula Overview

One way to count the number of cells containing data that meets one or more set conditions in Excel is to use a combination of the SUM and IF functions in an array formula.

In this tutorial example each row in the image above contains the wind speed and rainfall data for a storm event.

Using a SUM IF array formula will allow us to find those storms where the wind speed was greater than or equal to 30 or the rainfall was greater than or equal to 1.

The job of each part of the formula is:

  • The IF function contains the conditions we are testing for and the outcomes for those cells that meet one or more of these conditions

  • The array formula lets the IF function test for multiple conditions in a single cell, and, when a condition is met, the array formula determines what data the SUM function will total

  • the SUM function adds up the number of cells containing data that meets one of the set conditions

CSE Formulas

Array formulas are created by pressing the Ctrl, Shift, and Enter keys on the keyboard at the same time once the formula has been typed in.

Because of the keys pressed to create the array formula, they are sometimes referred to as CSE formulas.

OR vs. AND Conditional Operators

Excel has another function - the COUNTIFS function - which will count cells with data that meets multiple conditions, but the data must meet all of the conditions specified before it is counted.

To do this COUNTIFS uses the AND conditional operator, which means that the data must meet condition 1 AND condition 2 AND condition 3 etc. before it will be counted by the function.

The SUM IF Array formula, on the other hand, uses the OR conditional operator, which means that the data only has to meet condition 1 OR condition 2 OR condition 3 etc. to be counted by the formula.

The OR conditional operator used in the formula is the plus symbol +.

SUM IF Nested Formula Syntax and Arguments

The syntax for the SUM IF formula is:

=SUM ( IF ( logical_test, value_if_true, value_if_false ) )

  • Since the IF function is nested inside the SUM function, the entire IF function becomes the sole argument for the SUM function

The arguments for the IF function are:

  • logical_test - (required) a value or expression that is tested to see if it is true or false
  • value_if_true - (required) the value that is displayed if logical_test is true
  • value_if_false - (optional) the value that is displayed if logical_test is false

In this example:

  • the logical test searches cells D2 to D7 for wind speeds that are greater than or equal to 30 and it searches cells E2 to E7 for rainfall amounts that are greater than or equal to 1. These two ranges are linked by the + symbol - the OR operator

  • The value_if_true argument is 1

  • The value_if_false argument is zero ( 0 )

SUM IF Array Formula Example

Entering the Tutorial Data

  1. Enter the following data into cells D1 to E7 as seen in the image above:
       Wind Speed	   Rainfall
             25         	      1.0
             12	              0.6
             13	              0.9
             29	              1.6
             30	              0.99
             33	              1.5
  2. In cell D8 type " Count " (no quotes) since the formula will count up those storms that meet one of the two criteria

Entering the SUM IF Nested Formula

Since we are creating both a nested formula and an array formula, we will need to type the entire formula into a single worksheet cell.

Once you have entered the formula DO NOT press the Enter key on the keyboard or click on a different cell with the mouse as we need to turn the formula into an array formula.

  1. Click on cell E8 - the location where the formula results will be displayed

  2. Type the following:

    =SUM( IF( (D2:D7>=30) + (E2:E7>=1), 1, 0) )

Creating the Array Formula

  1. Press and hold down the Ctrl and Shift keys on the keyboard

  2. Press the Enter key on the keyboard to create the array formula

  3. The answer 4 should appear in cell E8 since the data for four storms - those in rows 2, 5, 6, and 7 - meet one of the two listed conditions for wind speed or rainfall

  4. If you click on cell E8 the complete array formula

    { =SUM( IF( (D2:D7>=30) + (E2:E7>=1), 1, 0) ) }

    can be seen in the formula bar above the worksheet

©2014 About.com. All rights reserved.