Summing up numbers from multiple columns (by Sumproduct?)
I'd like to sum up numbers from different columns which have the same titles, but not all of them have all the dates.
The Pivot tables involved are very complicated so I can't just merge them.
Is there any formula to help me with this? I assume sumproduct could help.
In the attached sample Excel file, if row 28 (16/12/2018), for example, is done manually, it will look like:
Cell B28: =SUM(B6, F5)
Cell C28: =SUM(G5, K4)
Cell D28: =SUM(C6, H5, L4)
microsoft-excel worksheet-function
asked Dec 19 '18 at 16:18
LWCLWC
146115
|
show 2 more comments
I'd like to sum up numbers from different columns which have the same titles, but not all of them have all the dates.
The Pivot tables involved are very complicated so I can't just merge them.
Is there any formula to help me with this? I assume sumproduct could help.
In the attached sample Excel file, if row 28 (16/12/2018), for example, is done manually, it will look like:
Cell B28: =SUM(B6, F5)
Cell C28: =SUM(G5, K4)
Cell D28: =SUM(C6, H5, L4)
microsoft-excel worksheet-function
asked Dec 19 '18 at 16:18
LWCLWC
146115
Can you please edit your question to let us know what you have tried, and where the problem is?
– cybernetic.nomad
Dec 19 '18 at 17:01
SUMIF() may be the formula you're looking for. How many pivot tables do you have? If, for example, you just want to total all of the cells for a given date in "Sum of Gross Revenue" columns, SUMIF can do this, although the more ranges/tables you have, the longer and messier the formula will become
– user2800
Dec 19 '18 at 17:19
Exactly, I don't want to make it manual. If they all had the same dates, I would have done one SUMIF to sum by column titles (which repeat themselves), for example=sumif(1:1, "Sum of Fee", 4:4)
– LWC
Dec 19 '18 at 18:50
If you use this formula in B24 and drag down the column, it will sum your gross revenues from Source 1 and Source 2. You can add additional similar SUMIF terms within the SUM to include other Source tables as well. This equation does not scale well, that's why I asked how many pivot tables you have. =SUM(SUMIF($A$4:$A$21,$A24,$B$4:$B$21),SUMIF($E$4:$E$21,$A24,$F$4:$F$21))
– user2800
Dec 20 '18 at 14:55
The number of pivot tables is dynamic and they don't always have all the columns (as shown in the screenshot)...
– LWC
Dec 20 '18 at 18:59
|
show 2 more comments
I'd like to sum up numbers from different columns which have the same titles, but not all of them have all the dates.
The Pivot tables involved are very complicated so I can't just merge them.
Is there any formula to help me with this? I assume sumproduct could help.
In the attached sample Excel file, if row 28 (16/12/2018), for example, is done manually, it will look like:
Cell B28: =SUM(B6, F5)
Cell C28: =SUM(G5, K4)
Cell D28: =SUM(C6, H5, L4)
microsoft-excel worksheet-function
asked Dec 19 '18 at 16:18
LWCLWC
146115
I'd like to sum up numbers from different columns which have the same titles, but not all of them have all the dates.
The Pivot tables involved are very complicated so I can't just merge them.
Is there any formula to help me with this? I assume sumproduct could help.
In the attached sample Excel file, if row 28 (16/12/2018), for example, is done manually, it will look like:
Cell B28: =SUM(B6, F5)
Cell C28: =SUM(G5, K4)
Cell D28: =SUM(C6, H5, L4)
microsoft-excel worksheet-function
microsoft-excel worksheet-function
asked Dec 19 '18 at 16:18
LWCLWC
146115
asked Dec 19 '18 at 16:18
LWCLWC
146115
edited Dec 21 '18 at 15:17
LWC
asked Dec 19 '18 at 16:18
LWCLWC
146115
asked Dec 19 '18 at 16:18
LWCLWC
146115
asked Dec 19 '18 at 16:18
LWCLWC
146115
146115
Can you please edit your question to let us know what you have tried, and where the problem is?
– cybernetic.nomad
Dec 19 '18 at 17:01
SUMIF() may be the formula you're looking for. How many pivot tables do you have? If, for example, you just want to total all of the cells for a given date in "Sum of Gross Revenue" columns, SUMIF can do this, although the more ranges/tables you have, the longer and messier the formula will become
– user2800
Dec 19 '18 at 17:19
Exactly, I don't want to make it manual. If they all had the same dates, I would have done one SUMIF to sum by column titles (which repeat themselves), for example=sumif(1:1, "Sum of Fee", 4:4)
– LWC
Dec 19 '18 at 18:50
If you use this formula in B24 and drag down the column, it will sum your gross revenues from Source 1 and Source 2. You can add additional similar SUMIF terms within the SUM to include other Source tables as well. This equation does not scale well, that's why I asked how many pivot tables you have. =SUM(SUMIF($A$4:$A$21,$A24,$B$4:$B$21),SUMIF($E$4:$E$21,$A24,$F$4:$F$21))
– user2800
Dec 20 '18 at 14:55
The number of pivot tables is dynamic and they don't always have all the columns (as shown in the screenshot)...
– LWC
Dec 20 '18 at 18:59
|
show 2 more comments
Can you please edit your question to let us know what you have tried, and where the problem is?
– cybernetic.nomad
Dec 19 '18 at 17:01
SUMIF() may be the formula you're looking for. How many pivot tables do you have? If, for example, you just want to total all of the cells for a given date in "Sum of Gross Revenue" columns, SUMIF can do this, although the more ranges/tables you have, the longer and messier the formula will become
– user2800
Dec 19 '18 at 17:19
Exactly, I don't want to make it manual. If they all had the same dates, I would have done one SUMIF to sum by column titles (which repeat themselves), for example=sumif(1:1, "Sum of Fee", 4:4)
– LWC
Dec 19 '18 at 18:50
If you use this formula in B24 and drag down the column, it will sum your gross revenues from Source 1 and Source 2. You can add additional similar SUMIF terms within the SUM to include other Source tables as well. This equation does not scale well, that's why I asked how many pivot tables you have. =SUM(SUMIF($A$4:$A$21,$A24,$B$4:$B$21),SUMIF($E$4:$E$21,$A24,$F$4:$F$21))
– user2800
Dec 20 '18 at 14:55
The number of pivot tables is dynamic and they don't always have all the columns (as shown in the screenshot)...
– LWC
Dec 20 '18 at 18:59
Can you please edit your question to let us know what you have tried, and where the problem is?
– cybernetic.nomad
Dec 19 '18 at 17:01
Can you please edit your question to let us know what you have tried, and where the problem is?
– cybernetic.nomad
Dec 19 '18 at 17:01
SUMIF() may be the formula you're looking for. How many pivot tables do you have? If, for example, you just want to total all of the cells for a given date in "Sum of Gross Revenue" columns, SUMIF can do this, although the more ranges/tables you have, the longer and messier the formula will become
– user2800
Dec 19 '18 at 17:19
SUMIF() may be the formula you're looking for. How many pivot tables do you have? If, for example, you just want to total all of the cells for a given date in "Sum of Gross Revenue" columns, SUMIF can do this, although the more ranges/tables you have, the longer and messier the formula will become
– user2800
Dec 19 '18 at 17:19
Exactly, I don't want to make it manual. If they all had the same dates, I would have done one SUMIF to sum by column titles (which repeat themselves), for example
=sumif(1:1, "Sum of Fee", 4:4)– LWC
Dec 19 '18 at 18:50
Exactly, I don't want to make it manual. If they all had the same dates, I would have done one SUMIF to sum by column titles (which repeat themselves), for example
=sumif(1:1, "Sum of Fee", 4:4)– LWC
Dec 19 '18 at 18:50
If you use this formula in B24 and drag down the column, it will sum your gross revenues from Source 1 and Source 2. You can add additional similar SUMIF terms within the SUM to include other Source tables as well. This equation does not scale well, that's why I asked how many pivot tables you have. =SUM(SUMIF($A$4:$A$21,$A24,$B$4:$B$21),SUMIF($E$4:$E$21,$A24,$F$4:$F$21))
– user2800
Dec 20 '18 at 14:55
If you use this formula in B24 and drag down the column, it will sum your gross revenues from Source 1 and Source 2. You can add additional similar SUMIF terms within the SUM to include other Source tables as well. This equation does not scale well, that's why I asked how many pivot tables you have. =SUM(SUMIF($A$4:$A$21,$A24,$B$4:$B$21),SUMIF($E$4:$E$21,$A24,$F$4:$F$21))
– user2800
Dec 20 '18 at 14:55
The number of pivot tables is dynamic and they don't always have all the columns (as shown in the screenshot)...
– LWC
Dec 20 '18 at 18:59
The number of pivot tables is dynamic and they don't always have all the columns (as shown in the screenshot)...
– LWC
Dec 20 '18 at 18:59
|
show 2 more comments
1 Answer
1
active
oldest
votes
Since you had specified a specific range for all the sum & date.. you may use the index()+ match() strategy and combine it to become "value1 + value2 + value3"
Initiate this formula in B26.
value1 is.. the sum value from source1 of the data defined in A26, if not found(error), then returns 0. Or ..
IFERROR(INDEX($B$4:$B$21,MATCH($A26,$A$4:$A$21,0)),0)
then value 2 is from source2, or :
IFERROR(INDEX($F$4:$F$20,MATCH($A26,$E$4:$E$20,0)),0)
and so does for value3 :
IFERROR(INDEX($K$4:$K$19,MATCH($A26,$J$4:$J$19,0)),0)
combining it.. total = value1+value2+value3
=IFERROR(INDEX($B$4:$B$21,MATCH($A26,$A$4:$A$21,0)),0)+IFERROR(INDEX($F$4:$F$20,MATCH($A26,$E$4:$E$20,0)),0)+
IFERROR(INDEX($K$4:$K$19,MATCH($A26,$J$4:$J$19,0)),0)
this one should be in cell B6.
hope it helps.
answered Jan 8 at 7:34
p._phidot_p._phidot_
57429
Thanks, but what's the advantage over sumif? It still doesn't support any number of pivot tables with dynamic columns (as shown in the screenshot).
– LWC
Jan 9 at 12:25
"advantage over sumif? " I'd rather say.. its an alternative.. sum with sumif will work too If you look at how the formula works.. both actually compares the source date 1st.. then load the revenue value to be sum up later (same as value1 + value2 + value3). || Technically, sumif can 'collects' the revenue if there is more than one match in each source.. ie.. 2 same dates, different row/value. Since this is not your case, it works the same. || The only way I see that to accommodate the dynamic row requirement.. ( which you should mention in the main question.. not comment) is to move the ..
– p._phidot_
Jan 10 at 6:40
.. A24:D23 table to say, R2:U21. a place where it is not in the same column as source1,2,3,.. then update this index-match formula to :=IFERROR(INDEX($B:$B,MATCH($R4,$A:$A,0)),0)+IFERROR(INDEX($F:$F,MATCH($R4,$E:$E,0)),0)+ IFERROR(INDEX($K:$K,MATCH($R4,$J:$J,0)),0)|| or you may put it in the A-D column and source1 in F-H column source2 in J-M column( an so on..), and shift the source1,2,3 columns ref accordingly.
– p._phidot_
Jan 10 at 6:47
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "3"
};
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%2fsuperuser.com%2fquestions%2f1385964%2fsumming-up-numbers-from-multiple-columns-by-sumproduct%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
Since you had specified a specific range for all the sum & date.. you may use the index()+ match() strategy and combine it to become "value1 + value2 + value3"
Initiate this formula in B26.
value1 is.. the sum value from source1 of the data defined in A26, if not found(error), then returns 0. Or ..
IFERROR(INDEX($B$4:$B$21,MATCH($A26,$A$4:$A$21,0)),0)
then value 2 is from source2, or :
IFERROR(INDEX($F$4:$F$20,MATCH($A26,$E$4:$E$20,0)),0)
and so does for value3 :
IFERROR(INDEX($K$4:$K$19,MATCH($A26,$J$4:$J$19,0)),0)
combining it.. total = value1+value2+value3
=IFERROR(INDEX($B$4:$B$21,MATCH($A26,$A$4:$A$21,0)),0)+IFERROR(INDEX($F$4:$F$20,MATCH($A26,$E$4:$E$20,0)),0)+
IFERROR(INDEX($K$4:$K$19,MATCH($A26,$J$4:$J$19,0)),0)
this one should be in cell B6.
hope it helps.
answered Jan 8 at 7:34
p._phidot_p._phidot_
57429
Thanks, but what's the advantage over sumif? It still doesn't support any number of pivot tables with dynamic columns (as shown in the screenshot).
– LWC
Jan 9 at 12:25
"advantage over sumif? " I'd rather say.. its an alternative.. sum with sumif will work too If you look at how the formula works.. both actually compares the source date 1st.. then load the revenue value to be sum up later (same as value1 + value2 + value3). || Technically, sumif can 'collects' the revenue if there is more than one match in each source.. ie.. 2 same dates, different row/value. Since this is not your case, it works the same. || The only way I see that to accommodate the dynamic row requirement.. ( which you should mention in the main question.. not comment) is to move the ..
– p._phidot_
Jan 10 at 6:40
.. A24:D23 table to say, R2:U21. a place where it is not in the same column as source1,2,3,.. then update this index-match formula to :=IFERROR(INDEX($B:$B,MATCH($R4,$A:$A,0)),0)+IFERROR(INDEX($F:$F,MATCH($R4,$E:$E,0)),0)+ IFERROR(INDEX($K:$K,MATCH($R4,$J:$J,0)),0)|| or you may put it in the A-D column and source1 in F-H column source2 in J-M column( an so on..), and shift the source1,2,3 columns ref accordingly.
– p._phidot_
Jan 10 at 6:47
add a comment |
Since you had specified a specific range for all the sum & date.. you may use the index()+ match() strategy and combine it to become "value1 + value2 + value3"
Initiate this formula in B26.
value1 is.. the sum value from source1 of the data defined in A26, if not found(error), then returns 0. Or ..
IFERROR(INDEX($B$4:$B$21,MATCH($A26,$A$4:$A$21,0)),0)
then value 2 is from source2, or :
IFERROR(INDEX($F$4:$F$20,MATCH($A26,$E$4:$E$20,0)),0)
and so does for value3 :
IFERROR(INDEX($K$4:$K$19,MATCH($A26,$J$4:$J$19,0)),0)
combining it.. total = value1+value2+value3
=IFERROR(INDEX($B$4:$B$21,MATCH($A26,$A$4:$A$21,0)),0)+IFERROR(INDEX($F$4:$F$20,MATCH($A26,$E$4:$E$20,0)),0)+
IFERROR(INDEX($K$4:$K$19,MATCH($A26,$J$4:$J$19,0)),0)
this one should be in cell B6.
hope it helps.
answered Jan 8 at 7:34
p._phidot_p._phidot_
57429
Thanks, but what's the advantage over sumif? It still doesn't support any number of pivot tables with dynamic columns (as shown in the screenshot).
– LWC
Jan 9 at 12:25
"advantage over sumif? " I'd rather say.. its an alternative.. sum with sumif will work too If you look at how the formula works.. both actually compares the source date 1st.. then load the revenue value to be sum up later (same as value1 + value2 + value3). || Technically, sumif can 'collects' the revenue if there is more than one match in each source.. ie.. 2 same dates, different row/value. Since this is not your case, it works the same. || The only way I see that to accommodate the dynamic row requirement.. ( which you should mention in the main question.. not comment) is to move the ..
– p._phidot_
Jan 10 at 6:40
.. A24:D23 table to say, R2:U21. a place where it is not in the same column as source1,2,3,.. then update this index-match formula to :=IFERROR(INDEX($B:$B,MATCH($R4,$A:$A,0)),0)+IFERROR(INDEX($F:$F,MATCH($R4,$E:$E,0)),0)+ IFERROR(INDEX($K:$K,MATCH($R4,$J:$J,0)),0)|| or you may put it in the A-D column and source1 in F-H column source2 in J-M column( an so on..), and shift the source1,2,3 columns ref accordingly.
– p._phidot_
Jan 10 at 6:47
add a comment |
Since you had specified a specific range for all the sum & date.. you may use the index()+ match() strategy and combine it to become "value1 + value2 + value3"
Initiate this formula in B26.
value1 is.. the sum value from source1 of the data defined in A26, if not found(error), then returns 0. Or ..
IFERROR(INDEX($B$4:$B$21,MATCH($A26,$A$4:$A$21,0)),0)
then value 2 is from source2, or :
IFERROR(INDEX($F$4:$F$20,MATCH($A26,$E$4:$E$20,0)),0)
and so does for value3 :
IFERROR(INDEX($K$4:$K$19,MATCH($A26,$J$4:$J$19,0)),0)
combining it.. total = value1+value2+value3
=IFERROR(INDEX($B$4:$B$21,MATCH($A26,$A$4:$A$21,0)),0)+IFERROR(INDEX($F$4:$F$20,MATCH($A26,$E$4:$E$20,0)),0)+
IFERROR(INDEX($K$4:$K$19,MATCH($A26,$J$4:$J$19,0)),0)
this one should be in cell B6.
hope it helps.
answered Jan 8 at 7:34
p._phidot_p._phidot_
57429
Since you had specified a specific range for all the sum & date.. you may use the index()+ match() strategy and combine it to become "value1 + value2 + value3"
Initiate this formula in B26.
value1 is.. the sum value from source1 of the data defined in A26, if not found(error), then returns 0. Or ..
IFERROR(INDEX($B$4:$B$21,MATCH($A26,$A$4:$A$21,0)),0)
then value 2 is from source2, or :
IFERROR(INDEX($F$4:$F$20,MATCH($A26,$E$4:$E$20,0)),0)
and so does for value3 :
IFERROR(INDEX($K$4:$K$19,MATCH($A26,$J$4:$J$19,0)),0)
combining it.. total = value1+value2+value3
=IFERROR(INDEX($B$4:$B$21,MATCH($A26,$A$4:$A$21,0)),0)+IFERROR(INDEX($F$4:$F$20,MATCH($A26,$E$4:$E$20,0)),0)+
IFERROR(INDEX($K$4:$K$19,MATCH($A26,$J$4:$J$19,0)),0)
this one should be in cell B6.
hope it helps.
answered Jan 8 at 7:34
p._phidot_p._phidot_
57429
answered Jan 8 at 7:34
p._phidot_p._phidot_
57429
answered Jan 8 at 7:34
p._phidot_p._phidot_
57429
answered Jan 8 at 7:34
p._phidot_p._phidot_
57429
57429
Thanks, but what's the advantage over sumif? It still doesn't support any number of pivot tables with dynamic columns (as shown in the screenshot).
– LWC
Jan 9 at 12:25
"advantage over sumif? " I'd rather say.. its an alternative.. sum with sumif will work too If you look at how the formula works.. both actually compares the source date 1st.. then load the revenue value to be sum up later (same as value1 + value2 + value3). || Technically, sumif can 'collects' the revenue if there is more than one match in each source.. ie.. 2 same dates, different row/value. Since this is not your case, it works the same. || The only way I see that to accommodate the dynamic row requirement.. ( which you should mention in the main question.. not comment) is to move the ..
– p._phidot_
Jan 10 at 6:40
.. A24:D23 table to say, R2:U21. a place where it is not in the same column as source1,2,3,.. then update this index-match formula to :=IFERROR(INDEX($B:$B,MATCH($R4,$A:$A,0)),0)+IFERROR(INDEX($F:$F,MATCH($R4,$E:$E,0)),0)+ IFERROR(INDEX($K:$K,MATCH($R4,$J:$J,0)),0)|| or you may put it in the A-D column and source1 in F-H column source2 in J-M column( an so on..), and shift the source1,2,3 columns ref accordingly.
– p._phidot_
Jan 10 at 6:47
add a comment |
Thanks, but what's the advantage over sumif? It still doesn't support any number of pivot tables with dynamic columns (as shown in the screenshot).
– LWC
Jan 9 at 12:25
"advantage over sumif? " I'd rather say.. its an alternative.. sum with sumif will work too If you look at how the formula works.. both actually compares the source date 1st.. then load the revenue value to be sum up later (same as value1 + value2 + value3). || Technically, sumif can 'collects' the revenue if there is more than one match in each source.. ie.. 2 same dates, different row/value. Since this is not your case, it works the same. || The only way I see that to accommodate the dynamic row requirement.. ( which you should mention in the main question.. not comment) is to move the ..
– p._phidot_
Jan 10 at 6:40
.. A24:D23 table to say, R2:U21. a place where it is not in the same column as source1,2,3,.. then update this index-match formula to :=IFERROR(INDEX($B:$B,MATCH($R4,$A:$A,0)),0)+IFERROR(INDEX($F:$F,MATCH($R4,$E:$E,0)),0)+ IFERROR(INDEX($K:$K,MATCH($R4,$J:$J,0)),0)|| or you may put it in the A-D column and source1 in F-H column source2 in J-M column( an so on..), and shift the source1,2,3 columns ref accordingly.
– p._phidot_
Jan 10 at 6:47
Thanks, but what's the advantage over sumif? It still doesn't support any number of pivot tables with dynamic columns (as shown in the screenshot).
– LWC
Jan 9 at 12:25
Thanks, but what's the advantage over sumif? It still doesn't support any number of pivot tables with dynamic columns (as shown in the screenshot).
– LWC
Jan 9 at 12:25
"advantage over sumif? " I'd rather say.. its an alternative.. sum with sumif will work too If you look at how the formula works.. both actually compares the source date 1st.. then load the revenue value to be sum up later (same as value1 + value2 + value3). || Technically, sumif can 'collects' the revenue if there is more than one match in each source.. ie.. 2 same dates, different row/value. Since this is not your case, it works the same. || The only way I see that to accommodate the dynamic row requirement.. ( which you should mention in the main question.. not comment) is to move the ..
– p._phidot_
Jan 10 at 6:40
"advantage over sumif? " I'd rather say.. its an alternative.. sum with sumif will work too If you look at how the formula works.. both actually compares the source date 1st.. then load the revenue value to be sum up later (same as value1 + value2 + value3). || Technically, sumif can 'collects' the revenue if there is more than one match in each source.. ie.. 2 same dates, different row/value. Since this is not your case, it works the same. || The only way I see that to accommodate the dynamic row requirement.. ( which you should mention in the main question.. not comment) is to move the ..
– p._phidot_
Jan 10 at 6:40
.. A24:D23 table to say, R2:U21. a place where it is not in the same column as source1,2,3,.. then update this index-match formula to :
=IFERROR(INDEX($B:$B,MATCH($R4,$A:$A,0)),0)+IFERROR(INDEX($F:$F,MATCH($R4,$E:$E,0)),0)+ IFERROR(INDEX($K:$K,MATCH($R4,$J:$J,0)),0) || or you may put it in the A-D column and source1 in F-H column source2 in J-M column( an so on..), and shift the source1,2,3 columns ref accordingly.– p._phidot_
Jan 10 at 6:47
.. A24:D23 table to say, R2:U21. a place where it is not in the same column as source1,2,3,.. then update this index-match formula to :
=IFERROR(INDEX($B:$B,MATCH($R4,$A:$A,0)),0)+IFERROR(INDEX($F:$F,MATCH($R4,$E:$E,0)),0)+ IFERROR(INDEX($K:$K,MATCH($R4,$J:$J,0)),0) || or you may put it in the A-D column and source1 in F-H column source2 in J-M column( an so on..), and shift the source1,2,3 columns ref accordingly.– p._phidot_
Jan 10 at 6:47
add a comment |
Thanks for contributing an answer to Super User!
- 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%2fsuperuser.com%2fquestions%2f1385964%2fsumming-up-numbers-from-multiple-columns-by-sumproduct%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
Can you please edit your question to let us know what you have tried, and where the problem is?
– cybernetic.nomad
Dec 19 '18 at 17:01
SUMIF() may be the formula you're looking for. How many pivot tables do you have? If, for example, you just want to total all of the cells for a given date in "Sum of Gross Revenue" columns, SUMIF can do this, although the more ranges/tables you have, the longer and messier the formula will become
– user2800
Dec 19 '18 at 17:19
Exactly, I don't want to make it manual. If they all had the same dates, I would have done one SUMIF to sum by column titles (which repeat themselves), for example
=sumif(1:1, "Sum of Fee", 4:4)– LWC
Dec 19 '18 at 18:50
If you use this formula in B24 and drag down the column, it will sum your gross revenues from Source 1 and Source 2. You can add additional similar SUMIF terms within the SUM to include other Source tables as well. This equation does not scale well, that's why I asked how many pivot tables you have. =SUM(SUMIF($A$4:$A$21,$A24,$B$4:$B$21),SUMIF($E$4:$E$21,$A24,$F$4:$F$21))
– user2800
Dec 20 '18 at 14:55
The number of pivot tables is dynamic and they don't always have all the columns (as shown in the screenshot)...
– LWC
Dec 20 '18 at 18:59