Array Formula

[DStan]

I use this array formula to filter and sum:
{=SUM(IF('BWP EXPENSE'!$A$2:$A$50000=10,IF('BWP EXPENSE'!$B$2:$B$50000>=740,IF('BWP EXPENSE'!$B$2:$B$50000<=749,IF('BWP EXPENSE'!$E$2:$E$50000=1999,IF('BWP EXPENSE'!$F$2:$F$50000<=7,'BWP EXPENSE'!$G$2:$G$50000,0),0),0))))}
My question is: IF('BWP EXPENSE'!$F$2:$F$50000<=7 where 7 = period or in our case January. I would like to be able instead of every month changing the "7" to the next period (I use this formula in several places) have all the IF statements refer to a particular cell that I change in one place. I would also use the same type reference for the year (see 1999).
Thank you for you time
David Stanley

 

[Aladin Akyurek]

On 2002-03-13 07:02, DStan wrote:
I use this array formula to filter and sum:
{=SUM(IF('BWP EXPENSE'!$A$2:$A$50000=10,IF('BWP EXPENSE'!$B$2:$B$50000>=740,IF('BWP EXPENSE'!$B$2:$B$50000<=749,IF('BWP EXPENSE'!$E$2:$E$50000=1999,IF('BWP EXPENSE'!$F$2:$F$50000<=7,'BWP EXPENSE'!$G$2:$G$50000,0),0),0))))}
My question is: IF('BWP EXPENSE'!$F$2:$F$50000<=7 where 7 = period or in our case January. I would like to be able instead of every month changing the "7" to the next period (I use this formula in several places) have all the IF statements refer to a particular cell that I change in one place. I would also use the same type reference for the year (see 1999).
Thank you for you time
David Stanley

David,

Are you using this formula in a single or multiple cells?

Do you really have data that fills the range 1 to 50000?

Aladin

 

[Dave Patton]

try

=SUM(($A$2:$A$5=10)*($B$2:$B$5>=740)*($B$2:$B$5<=749)*($E$2:$E$5=I2)*($F$2:$F$5=I3)*($G$2:$G$5))

I shortened your ranges to test.
Revise the criteria cells as necessary.

 

[DStan]

On 2002-03-13 07:40, Aladin Akyurek wrote:

On 2002-03-13 07:02, DStan wrote:
I use this array formula to filter and sum:
{=SUM(IF('BWP EXPENSE'!$A$2:$A$50000=10,IF('BWP EXPENSE'!$B$2:$B$50000>=740,IF('BWP EXPENSE'!$B$2:$B$50000<=749,IF('BWP EXPENSE'!$E$2:$E$50000=1999,IF('BWP EXPENSE'!$F$2:$F$50000<=7,'BWP EXPENSE'!$G$2:$G$50000,0),0),0))))}
My question is: IF('BWP EXPENSE'!$F$2:$F$50000<=7 where 7 = period or in our case January. I would like to be able instead of every month changing the "7" to the next period (I use this formula in several places) have all the IF statements refer to a particular cell that I change in one place. I would also use the same type reference for the year (see 1999).
Thank you for you time
David Stanley

David,

Are you using this formula in a single or multiple cells?

Do you really have data that fills the range 1 to 50000?

Aladin

Aladin

This is a single cell formula. I am importing data from access database and used large number because 5000 wasn't enough so I added a zero. Should I reduce this number?

 

[Aladin Akyurek]

On 2002-03-13 12:15, DStan wrote:

On 2002-03-13 07:40, Aladin Akyurek wrote:

On 2002-03-13 07:02, DStan wrote:
I use this array formula to filter and sum:
{=SUM(IF('BWP EXPENSE'!$A$2:$A$50000=10,IF('BWP EXPENSE'!$B$2:$B$50000>=740,IF('BWP EXPENSE'!$B$2:$B$50000<=749,IF('BWP EXPENSE'!$E$2:$E$50000=1999,IF('BWP EXPENSE'!$F$2:$F$50000<=7,'BWP EXPENSE'!$G$2:$G$50000,0),0),0))))}
My question is: IF('BWP EXPENSE'!$F$2:$F$50000<=7 where 7 = period or in our case January. I would like to be able instead of every month changing the "7" to the next period (I use this formula in several places) have all the IF statements refer to a particular cell that I change in one place. I would also use the same type reference for the year (see 1999).
Thank you for you time
David Stanley

David,

Are you using this formula in a single or multiple cells?

Do you really have data that fills the range 1 to 50000?

Aladin

Aladin

This is a single cell formula. I am importing data from access database and used large number because 5000 wasn't enough so I added a zero. Should I reduce this number?

There is indeed no need to have the formula to look at unused cells. And, you have data that apparently changes over time. That calls for dynamically computing the ranges of interest.

It looks like column G in BWP EXPENSE is of numeric type, which we can use for that purpose.

May I suggest that you modify the name of the sheet to just BWPexpense (no space), although it doesn't matter at all how it's called to what follows.

Activate Insert|Name|Define.
Type "EndRow" in the Names in Workbook box.
Enter as formula in the 'Refers To' box:

=MATCH(9.99999999999999E+307,BWPexpense!$G:$G)

Click OK.

Activate the sheet where you want the formula.

In E1 enter: 10
In E2 enter: 740
In E3 enter: 749
In E4 enter: 1999
In E5 enter: 7

These are criteria/conditions that you appear to use.

You can also use, as noted by Dave Patton, a normally entered SUMPRODUCT formula instead of an array formula, in which the definite references are replaced with dynamically computed ranges by using the OFFSET worksheet function:

=SUMPRODUCT((OFFSET(BWPexpense!$A$2,0,0,EndRow-1,1)=E1)*(OFFSET(BWPexpense!$B$2,0,0,EndRow-1,1)>=E2)*(OFFSET(BWPexpense!$B$2,0,0,EndRow-1,1)<=E3)*(OFFSET(BWPexpense!$E$2,0,0,EndRow-1,1)=E4)*(OFFSET(BWPexpense!$F$2,0,0,EndRow-1,1)<=E5),OFFSET(BWPexpense!$G$2,0,0,EndRow-1,1))

That should do it.

You should not need to touch this formula whenever you choose a defferent set of criteria/conditions and/or the number of records change in the data area.

Aladin