Thanks:  0
Likes:  0

# Thread: Keeping Constant Values in Formulae

1. 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 ]

2. 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.

3. Hi Nathan,

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

can you post the formula ?

thanks
Chris

4. 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

5. 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.

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

you have an =SUM(IF( array formula

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

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•