Keeping Constant Values in Formulae

[Nathanm]

I am using a 2 condition sumif and want to copy the formula down to refer to different if criteria. The problem is that the data range and sum range keep altering down a line.

How do I do this?

[ This Message was edited by: Nathanm on 2002-04-11 04:57 ]

[Aladin Akyurek]

On 2002-04-11 04:57, Nathanm wrote:
I am using a 2 condition sumif and want to copy the formula down to refer to different if criteria. The problem is that the data range and sum range keep altering down a line.

How do I do this?

[ This Message was edited by: Nathanm on 2002-04-11 04:57 ]

Freeze the ranges like in

=SUMIF($A$1:$A$10,">"&E1,$B$1:$B$10)

or give the target ranges a name.

[Chris Davison]

Hi Nathan,

I'd be interested to see those 2 conditions within =SUMIF

can you post the formula ?

thanks
Chris

[Nathanm]

SUMIF formula

{=SUM(IF('Average REnts'!$G$2:$G$7004='Average Rents Summary'!C11,IF('Average REnts'!$E$2:$E$7004='Average Rents Summary'!A11,'Average REnts'!$T$2:$T$7004)))}

I was using this to look at a list of data I had exported into excel for Performance Indicator purposes and wanted to pick up some averages on the basis of the owning company and no. of bedroooms - I specified this in cells C11 and A11.

Hope that is sufficient

[Aladin Akyurek]

On 2002-04-15 02:03, Nathanm wrote:
SUMIF formula

{=SUM(IF('Average REnts'!$G$2:$G$7004='Average Rents Summary'!C11,IF('Average REnts'!$E$2:$E$7004='Average Rents Summary'!A11,'Average REnts'!$T$2:$T$7004)))}

I was using this to look at a list of data I had exported into excel for Performance Indicator purposes and wanted to pick up some averages on the basis of the owning company and no. of bedroooms - I specified this in cells C11 and A11.

Hope that is sufficient

Looking at the formula, I have the impression that you want to sum the values in 'Average REnts'!$T$2:$T$7004 for a given company and a given number of bedrooms. If so, you should have the conditions Companies and Number of bedrooms of interest in cells of their own in a separate worksheet, say in A from A2 on and B from B2 on. You could enter the above array-formula or another equivalent one in C2 and copy down. Another tip: Using dynamic name ranges would allow you to restrict the formulas to only actually used ranges in Average Rents and Average Rents Summary.

Aladin







[ This Message was edited by: Aladin Akyurek on 2002-04-15 12:22 ]

[Chris Davison]

you had me intrigued....

you have an =SUM(IF( array formula

I was getting all excited thinking you had an actual =SUMIF( formula