Power Query Course in Spanish
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Keeping Constant Values in Formulae

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,646
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    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. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Nathan,

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

    can you post the formula ?

    thanks
    Chris

  4. #4
    New Member
    Join Date
    Apr 2002
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #5
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,646
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    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 ]

  6. #6
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    you had me intrigued....

    you have an =SUM(IF( array formula

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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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