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