Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    Mar 2006
    Location
    Philadelphia, PA
    Posts
    30
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Sum values based on a Named Range

    Hello, I'd like to set up a condition that would calculate values based on a named range. I can't figure out how to set it up. Currently, I'm using an if statement, but it's limiting.

    The attached spread sheet has budget entries and next to it is the 12 month budget spread. Based on the Qtr listed in Column H, I'd like to add that amount to the months that fall within the Quarter indicated.
    Attached Files Attached Files

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    ab,

    I can't currently figure out how to do it with "a" named range but might I suggest a couple of changes that may make your calcs more efficient and slightly more accurate.
    Insert a row #2 and over each of the three columns in a quarter place just the quarter number, or optionally you could just delete the "Q's".
    In L5 =IF($H5=L$2,ROUND($G5/3,0),0)
    In M5 =L5
    In N5 =IF($H5=N$2,$G5-SUM(L5:M5),0)
    ab2537.JPG
    These 3 formulas can be Copied int each quarter and then the whole thing filled down.
    Note: if you do insert a row 2 you can hide it once the numbers are in place.
    Attached Files Attached Files
    Last edited by RetiredGeek; 2013-02-25 at 16:26.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. The Following User Says Thank You to RetiredGeek For This Useful Post:

    ab2537 (2013-02-26)

  4. #3
    Lounger
    Join Date
    Mar 2006
    Location
    Philadelphia, PA
    Posts
    30
    Thanks
    12
    Thanked 0 Times in 0 Posts
    RG, thanks so much!!

  5. #4
    Lounger
    Join Date
    Apr 2012
    Location
    Abergavenny, Wales, UK
    Posts
    25
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Hi

    If you mean that you are wanting to use the Table Structured references then an alternative would be to copy the formula below
    and paste to range L4:W20 of your existing sheet.

    =Table1[@Total]/3*(Table1[@QTR]=--RIGHT(L$2))

    Note: This will still give the rounding differences as opposed to RG's method - but if it is only Budget figures, this may not matter too much to you.
    Regards
    Roger Govier
    Microsoft Excel MVP

Tags for this Thread

Posting Permissions

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