CODEWITHSUNDEEP
excelsumrange
GailN
GailN

Reputation: 1

Excel - calculate cumulative figures based on lookup

Simple table within employee names going down in column A and working day numbers across the top from column B - stated as 1,2,3,4 etc up to 31.

Each day we enter hours worked.

At the end of each row I want a sum for the hours worked based on a specified day. Ie if I enter working day = 15 in a cell at top of the sum column it will return totals for days 1-15.

Upvotes: 0

Views: 157

Answers (2)

Matt
Matt

Reputation: 21

You want to use SUM(OFFSET(....)) here.

Assuming your "Working Days" cell is AH1 and your day numbers are in B:AF, an formula for each row (starting in row 2) would be:

=SUM(OFFSET(B2,0,0,1,$AH$1)).

This uses OFFSET to return a dynamic range so that you can sum up hours worked from day 1 to whatever day you put in cell AH1.

Upvotes: 0

BigBen
BigBen

Reputation: 50162

Use the SUMIF function and the ampersand & to reference the cell containing the specified working day.

If the 1-31 are in columns B:AF, and the 15 reference is in AG1, try,

=SUMIF($B$1:$AF$1,"<="&$AG$1,B2:AF2)

in AG2 and drag down as needed.

Upvotes: 2

Related Questions