Search code examples
excelsum

Excel Sum X amount of columns depending on a value in another cell

Is there a way that you can use sum() in excel and that sums up x amount of columns depending on number given in another cell.

See below for example Example of how it's today

In this picture I have columns B:M were I put in Actual figures. And in columns T:AE I have budget figures.

In this example I have actual figures JAN-MAY and would like to compare them vs budget JAN-MAY.

How I do today is that I go in to column AF and drag so it's =SUM(T6:X6) so it only takes T:X in this case. But I would like that cell AF has a formula in it were it looks at cell AH:3 and sees that it's number 5. Therefore AF should sum(T;U;V;W;X(5columns))

Hope you understand my problem and have a solution!! :)

Solution

  • You can use the OFFSET function to determine the number of columns to SUM. In your example, AH3 is your offset amount.

    In AF6 you can place this formula:

    =SUM(T6:OFFSET(T6,0,AH3-1))

    Which breaks down to

    =SUM(T6:OFFSET(Start at T6, Offset 0 rows, Offset columns the value of AH3 and subtract one for a total of five columns))