# Thread: Sum values based on a Named Range

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

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

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

ab2537 (2013-02-26)

4. RG, thanks so much!!

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

#### Posting Permissions

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