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.
excel sum range
asked Nov 13 '18 at 20:50
GailNGailN
1
add a comment |
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.
excel sum range
asked Nov 13 '18 at 20:50
GailNGailN
1
2
Have you tried usingSUMIF?
– BigBen
Nov 13 '18 at 20:51
Not sure how I’d construct a sum if to do this? I want to be able to just change the mtd reference and it recalculate all rows.
– GailN
Nov 13 '18 at 20:55
add a comment |
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.
excel sum range
asked Nov 13 '18 at 20:50
GailNGailN
1
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.
excel sum range
excel sum range
asked Nov 13 '18 at 20:50
GailNGailN
1
asked Nov 13 '18 at 20:50
GailNGailN
1
asked Nov 13 '18 at 20:50
GailNGailN
1
asked Nov 13 '18 at 20:50
GailNGailN
1
asked Nov 13 '18 at 20:50
GailNGailN
1
1
2
Have you tried usingSUMIF?
– BigBen
Nov 13 '18 at 20:51
Not sure how I’d construct a sum if to do this? I want to be able to just change the mtd reference and it recalculate all rows.
– GailN
Nov 13 '18 at 20:55
add a comment |
2
Have you tried usingSUMIF?
– BigBen
Nov 13 '18 at 20:51
Not sure how I’d construct a sum if to do this? I want to be able to just change the mtd reference and it recalculate all rows.
– GailN
Nov 13 '18 at 20:55
2
2
Have you tried using
SUMIF?– BigBen
Nov 13 '18 at 20:51
Have you tried using
SUMIF?– BigBen
Nov 13 '18 at 20:51
Not sure how I’d construct a sum if to do this? I want to be able to just change the mtd reference and it recalculate all rows.
– GailN
Nov 13 '18 at 20:55
Not sure how I’d construct a sum if to do this? I want to be able to just change the mtd reference and it recalculate all rows.
– GailN
Nov 13 '18 at 20:55
add a comment |
2 Answers
2
active
oldest
votes
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.
answered Nov 13 '18 at 21:02
BigBenBigBen
6,3172618
add a comment |
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.
answered Nov 13 '18 at 21:08
MattMatt
111
add a comment |
Your Answer
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53289299%2fexcel-calculate-cumulative-figures-based-on-lookup%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
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.
answered Nov 13 '18 at 21:02
BigBenBigBen
6,3172618
add a comment |
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.
answered Nov 13 '18 at 21:02
BigBenBigBen
6,3172618
add a comment |
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.
answered Nov 13 '18 at 21:02
BigBenBigBen
6,3172618
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.
answered Nov 13 '18 at 21:02
BigBenBigBen
6,3172618
answered Nov 13 '18 at 21:02
BigBenBigBen
6,3172618
answered Nov 13 '18 at 21:02
BigBenBigBen
6,3172618
answered Nov 13 '18 at 21:02
BigBenBigBen
6,3172618
6,3172618
add a comment |
add a comment |
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.
answered Nov 13 '18 at 21:08
MattMatt
111
add a comment |
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.
answered Nov 13 '18 at 21:08
MattMatt
111
add a comment |
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.
answered Nov 13 '18 at 21:08
MattMatt
111
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.
answered Nov 13 '18 at 21:08
MattMatt
111
answered Nov 13 '18 at 21:08
MattMatt
111
answered Nov 13 '18 at 21:08
MattMatt
111
answered Nov 13 '18 at 21:08
MattMatt
111
111
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53289299%2fexcel-calculate-cumulative-figures-based-on-lookup%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
2
Have you tried using
SUMIF?– BigBen
Nov 13 '18 at 20:51
Not sure how I’d construct a sum if to do this? I want to be able to just change the mtd reference and it recalculate all rows.
– GailN
Nov 13 '18 at 20:55