# formula to sum columns based a value in a given column

Hi Experts (excel 2007)

I need a sum formula to sum across the data in a set number of columns based on what number i enter in column U

So if i enter in column u 4 then >

starting at column 6C (always) sum columns 6C:6F and enter result in column P

So if i enter in Column u 7 then>

Staring at column 6C (always) sum Columns 6C:6I etc....
###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

IT Services ConsultantCommented:
Hi,

Sorry, how does a value of 4 (or 7) relate to the specific starting columns/rows you have stated?

Also, where is 4 entered in column U?  Which row?

BFN,

fp.
0
JuniorAuthor Commented:
apologies fp

Assume 4 is entered in row 7 column U

and then we want to sum up the data in column C7:F7...
0
IT Services ConsultantCommented:
OK, thanks.

So, with a value of 4 in cell [U7], you wish to total the range [C7:F7] (4 cells), & place the result in, I'm guessing, cell [P7].

...and with a value of 7 in cell [U7], you wish to total the range [C7:I7) (7 cells).

Here is one method...

Please enter this formula in cell [P7]:

Obviously, if you enter a value of 14 or over in cell [U7] then you will find a 'circular reference' error.

I have attached a working example.

BFN,

fp.
Q-28244877.xls
0

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

JuniorAuthor Commented:
thanks for the feedack fp
0
IT Services ConsultantCommented:
You're welcome.
0
Commented:
Hi,

To be able to use the formula in different rows
pls use

=SUM(INDIRECT("R"&ROW(P6)&"C"&3&":"&"R"&ROW(P6)&"C"&2+P6,FALSE))

Regards
0
IT Services ConsultantCommented:
...or, place this formula in cell [U7]: