Thursday, December 22, 2005

CTRL SHIFT ENTER Formulas in Excel

{=SUM((OFFSET(DB,1,$C$48-1,ROWS(data)-1,1)=$AN7)* (OFFSET(DB,1,$C$57-1,ROWS(data)-1,1)=TRUE)* (OFFSET(data,1,$C$20-1,ROWS(data)-1,1)=BH$2)* OFFSET(DB,1,$C$50-1,ROWS(data)-1,1))}

This is an example of a formula I am working with at the moment. It displays the power of a CTRL SHIFT ENTER formula.

Basically what it allows you to do is to sum specific columns (identified by the OFFSET function) of a database named DB (array of rows and columns of data) conditional on specific requirements.

In its basic form the above formula says to sum a column of data if the values in 3 other relative columns are specific values or TRUE.

This is basically a way of performing a SUMIF for more than one condition.
Let me explain.
A SUMIF formula uses the syntax Lookup_Range,Criteria,Result_Range
This is great if you want to look through a column for specific value and sum the results from an adjacent column.

But what if you want to have two, or more, criteria?

That is where formula like this one performs magnificently. In effect the * s between the parts of the formula act like & s and join the parts of the formula.
So this formula sums from the column OFFSET(DB,1,$C$50-1,ROWS(data)-1,1) where the value in the first range OFFSET(DB,1,$C$48-1,ROWS(data)-1,1) equals the value in $AN7 AND where the value in the second range OFFSET(DB,1,$C$57-1,ROWS(data)-1,1) equals TRUE AND where the value in the third column OFFSET(data,1,$C$20-1,ROWS(data)-1,1) equals the value in cell BH$2.

And to make it all work, hit CTRL SHIFT ENTER when entering or editing the formula.


Posted by: Jethro | 4:54 PM |