In Excel 2007 the the “COUNTIFS” and “SUMIFS” Formulas had replace the Array Formula in Excel older version.

“COUNTIFS” and “SUMIFS” formula allows you to stipulate multiple criteria which equivalent to Array Formula.

The Syntax for COUNTIFS:
=COUNTIFS(criteria_range_1, criteria_1, criteria_range_2, criteria_2,……. criteria_range_n, criteria_n),

The Syntax for SUMIFS:
=SUMIFS(sum_range,critera_range_1,criteria_1,criteria_range_2,criteria_2…..and so on if required)

Sample data and Example below:

Month-----Region-----Sales
Jan--------South------200
Jan--------East-------300
Jan--------West------400
Feb--------East-------100
Feb--------North-----200
Feb--------West------300
March-----East-------100
March-----South-----300
March-----North-----600



Firstly, define the Name Manager for “Month, “Region” and “Sales”

Question:

To calculate the Sales where the Month = Jan and the sales > 100
=COUNTIFS(Month, "Jan" ,Sales,">100") result= 3

To total up the Sales where the Month = Jan and the sales > 100
=SUMIFS(Sales, Month, "Jan", Sales, ">100") result =(200+300+400) = 900

For the above scenario if you using Excel 2007 version below you can perform the above calculation by using Array Formula as follow:

To calculate the Sales where the Month = Jan and the sales > 100
{=SUM((Region="West")*(Sales>100)) result=3}

To total up the Sales where the Month = Jan and the sales > 100
{=SUM((Month="Jan")*(Sales>100)*(Sales)) = 900}

Note: For the Array Formula when you run you need to press “Ctrl+Shifted+Enter” key.
For the “{ ……}” bracket do not need to keying as this “{}” will generated by Excel while you press the “Ctrl+Shifted+Enter” key.
The above Array Formula applicable to Excel 2007 as well.