Sum values based on a Named Range

Topic

New Reply

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.

Reply | Quote

Replies

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 [noparse]=IF($H5=L$2,ROUND($G5/3,0),0)[/noparse]
In M5 [noparse]=L5[/noparse]
In N5 [noparse]=IF($H5=N$2,$G5-SUM(L5:M5),0)[/noparse]
33247-ab2537
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.

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

RG, thanks so much!!

Reply | Quote

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.

Reply | Quote