Mixing
excel/google sheets: How to sum values seperated by Colon(:)
Can you help me?
How to sum values separated by colon
A1 A2 A3
3:0 5:9 4:5
To get sum like - 12:14
excel google-sheets
edited Nov 20 '18 at 15:57
Scott Craner
90.2k82550
asked Nov 20 '18 at 15:40
Valeriy Valeriy
41
add a comment |
Can you help me?
How to sum values separated by colon
A1 A2 A3
3:0 5:9 4:5
To get sum like - 12:14
excel google-sheets
edited Nov 20 '18 at 15:57
Scott Craner
90.2k82550
asked Nov 20 '18 at 15:40
Valeriy Valeriy
41
1
If its a one time thing, you can transpose your data, use text to column to separate the values on the:into two columns. then sum each column and concatenate the result.
– cybernetic.nomad
Nov 20 '18 at 15:50
add a comment |
Can you help me?
How to sum values separated by colon
A1 A2 A3
3:0 5:9 4:5
To get sum like - 12:14
excel google-sheets
edited Nov 20 '18 at 15:57
Scott Craner
90.2k82550
asked Nov 20 '18 at 15:40
Valeriy Valeriy
41
Can you help me?
How to sum values separated by colon
A1 A2 A3
3:0 5:9 4:5
To get sum like - 12:14
excel google-sheets
excel google-sheets
edited Nov 20 '18 at 15:57
Scott Craner
90.2k82550
asked Nov 20 '18 at 15:40
Valeriy Valeriy
41
edited Nov 20 '18 at 15:57
Scott Craner
90.2k82550
asked Nov 20 '18 at 15:40
Valeriy Valeriy
41
edited Nov 20 '18 at 15:57
Scott Craner
90.2k82550
edited Nov 20 '18 at 15:57
Scott Craner
90.2k82550
edited Nov 20 '18 at 15:57
Scott Craner
90.2k82550
90.2k82550
asked Nov 20 '18 at 15:40
Valeriy Valeriy
41
asked Nov 20 '18 at 15:40
Valeriy Valeriy
41
asked Nov 20 '18 at 15:40
Valeriy Valeriy
41
41
1
If its a one time thing, you can transpose your data, use text to column to separate the values on the:into two columns. then sum each column and concatenate the result.
– cybernetic.nomad
Nov 20 '18 at 15:50
add a comment |
1
If its a one time thing, you can transpose your data, use text to column to separate the values on the:into two columns. then sum each column and concatenate the result.
– cybernetic.nomad
Nov 20 '18 at 15:50
1
1
If its a one time thing, you can transpose your data, use text to column to separate the values on the
: into two columns. then sum each column and concatenate the result.– cybernetic.nomad
Nov 20 '18 at 15:50
If its a one time thing, you can transpose your data, use text to column to separate the values on the
: into two columns. then sum each column and concatenate the result.– cybernetic.nomad
Nov 20 '18 at 15:50
add a comment |
2 Answers
2
active
oldest
votes
Use SUMPRODUCT and parse the string:
=SUMPRODUCT(--LEFT(A1:A3,FIND(":",A1:A3)-1))&":"&SUMPRODUCT(--MID(A1:A3,FIND(":",A1:A3)+1,LEN(A1:A3)))
answered Nov 20 '18 at 16:08
Scott CranerScott Craner
90.2k82550
add a comment |
There are probably more clever ways to do it than this, but the most obvious way that comes to mind is to split the string into two components: one to the left of the colon and one to the right of the colon. You could do this in separate cells just to the side of the data you are working on.
For example, assume you have in cells A1, A2, and A3, the values "7:555", "88:77", and "34:987" respectively.
- In columns B1, you could put the function =NUMBERVALUE(LEFT(A1, FIND(":", A1) - 1)) to split out the left part of the string and convert it to a number.
- Then copy this formula down column B to match the number of rows in column A
- In Columns c1, c2, and C3, you could put the function =NUMBERVALUE(RIGHT(A1, LEN(A1) - FIND(":", A1)))
- Then copy this formula down column C to match the number of rows in column A
- Finally, just do a sum on column B to sum the left hand side and sum on column C to get the right hand side
Bonus, if you didn't want to show your work, you could hide columns B and C.
Super bonus, and probably over-thinking it, you could be a VBA macro.
answered Nov 20 '18 at 16:01
bigdavey71bigdavey71
11
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%2f53396528%2fexcel-google-sheets-how-to-sum-values-seperated-by-colon%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 SUMPRODUCT and parse the string:
=SUMPRODUCT(--LEFT(A1:A3,FIND(":",A1:A3)-1))&":"&SUMPRODUCT(--MID(A1:A3,FIND(":",A1:A3)+1,LEN(A1:A3)))
answered Nov 20 '18 at 16:08
Scott CranerScott Craner
90.2k82550
add a comment |
Use SUMPRODUCT and parse the string:
=SUMPRODUCT(--LEFT(A1:A3,FIND(":",A1:A3)-1))&":"&SUMPRODUCT(--MID(A1:A3,FIND(":",A1:A3)+1,LEN(A1:A3)))
answered Nov 20 '18 at 16:08
Scott CranerScott Craner
90.2k82550
add a comment |
Use SUMPRODUCT and parse the string:
=SUMPRODUCT(--LEFT(A1:A3,FIND(":",A1:A3)-1))&":"&SUMPRODUCT(--MID(A1:A3,FIND(":",A1:A3)+1,LEN(A1:A3)))
answered Nov 20 '18 at 16:08
Scott CranerScott Craner
90.2k82550
Use SUMPRODUCT and parse the string:
=SUMPRODUCT(--LEFT(A1:A3,FIND(":",A1:A3)-1))&":"&SUMPRODUCT(--MID(A1:A3,FIND(":",A1:A3)+1,LEN(A1:A3)))
answered Nov 20 '18 at 16:08
Scott CranerScott Craner
90.2k82550
answered Nov 20 '18 at 16:08
Scott CranerScott Craner
90.2k82550
answered Nov 20 '18 at 16:08
Scott CranerScott Craner
90.2k82550
answered Nov 20 '18 at 16:08
Scott CranerScott Craner
90.2k82550
90.2k82550
add a comment |
add a comment |
There are probably more clever ways to do it than this, but the most obvious way that comes to mind is to split the string into two components: one to the left of the colon and one to the right of the colon. You could do this in separate cells just to the side of the data you are working on.
For example, assume you have in cells A1, A2, and A3, the values "7:555", "88:77", and "34:987" respectively.
- In columns B1, you could put the function =NUMBERVALUE(LEFT(A1, FIND(":", A1) - 1)) to split out the left part of the string and convert it to a number.
- Then copy this formula down column B to match the number of rows in column A
- In Columns c1, c2, and C3, you could put the function =NUMBERVALUE(RIGHT(A1, LEN(A1) - FIND(":", A1)))
- Then copy this formula down column C to match the number of rows in column A
- Finally, just do a sum on column B to sum the left hand side and sum on column C to get the right hand side
Bonus, if you didn't want to show your work, you could hide columns B and C.
Super bonus, and probably over-thinking it, you could be a VBA macro.
answered Nov 20 '18 at 16:01
bigdavey71bigdavey71
11
add a comment |
There are probably more clever ways to do it than this, but the most obvious way that comes to mind is to split the string into two components: one to the left of the colon and one to the right of the colon. You could do this in separate cells just to the side of the data you are working on.
For example, assume you have in cells A1, A2, and A3, the values "7:555", "88:77", and "34:987" respectively.
- In columns B1, you could put the function =NUMBERVALUE(LEFT(A1, FIND(":", A1) - 1)) to split out the left part of the string and convert it to a number.
- Then copy this formula down column B to match the number of rows in column A
- In Columns c1, c2, and C3, you could put the function =NUMBERVALUE(RIGHT(A1, LEN(A1) - FIND(":", A1)))
- Then copy this formula down column C to match the number of rows in column A
- Finally, just do a sum on column B to sum the left hand side and sum on column C to get the right hand side
Bonus, if you didn't want to show your work, you could hide columns B and C.
Super bonus, and probably over-thinking it, you could be a VBA macro.
answered Nov 20 '18 at 16:01
bigdavey71bigdavey71
11
add a comment |
There are probably more clever ways to do it than this, but the most obvious way that comes to mind is to split the string into two components: one to the left of the colon and one to the right of the colon. You could do this in separate cells just to the side of the data you are working on.
For example, assume you have in cells A1, A2, and A3, the values "7:555", "88:77", and "34:987" respectively.
- In columns B1, you could put the function =NUMBERVALUE(LEFT(A1, FIND(":", A1) - 1)) to split out the left part of the string and convert it to a number.
- Then copy this formula down column B to match the number of rows in column A
- In Columns c1, c2, and C3, you could put the function =NUMBERVALUE(RIGHT(A1, LEN(A1) - FIND(":", A1)))
- Then copy this formula down column C to match the number of rows in column A
- Finally, just do a sum on column B to sum the left hand side and sum on column C to get the right hand side
Bonus, if you didn't want to show your work, you could hide columns B and C.
Super bonus, and probably over-thinking it, you could be a VBA macro.
answered Nov 20 '18 at 16:01
bigdavey71bigdavey71
11
There are probably more clever ways to do it than this, but the most obvious way that comes to mind is to split the string into two components: one to the left of the colon and one to the right of the colon. You could do this in separate cells just to the side of the data you are working on.
For example, assume you have in cells A1, A2, and A3, the values "7:555", "88:77", and "34:987" respectively.
- In columns B1, you could put the function =NUMBERVALUE(LEFT(A1, FIND(":", A1) - 1)) to split out the left part of the string and convert it to a number.
- Then copy this formula down column B to match the number of rows in column A
- In Columns c1, c2, and C3, you could put the function =NUMBERVALUE(RIGHT(A1, LEN(A1) - FIND(":", A1)))
- Then copy this formula down column C to match the number of rows in column A
- Finally, just do a sum on column B to sum the left hand side and sum on column C to get the right hand side
Bonus, if you didn't want to show your work, you could hide columns B and C.
Super bonus, and probably over-thinking it, you could be a VBA macro.
answered Nov 20 '18 at 16:01
bigdavey71bigdavey71
11
answered Nov 20 '18 at 16:01
bigdavey71bigdavey71
11
answered Nov 20 '18 at 16:01
bigdavey71bigdavey71
11
answered Nov 20 '18 at 16:01
bigdavey71bigdavey71
11
11
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%2f53396528%2fexcel-google-sheets-how-to-sum-values-seperated-by-colon%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
1
If its a one time thing, you can transpose your data, use text to column to separate the values on the
:into two columns. then sum each column and concatenate the result.– cybernetic.nomad
Nov 20 '18 at 15:50