EXCEL: SUMIFS with multiple identifiersHow to create Excel (.XLS and .XLSX) file in C# without installing Ms Office?How do I properly clean up Excel interop objects?How to create strings containing double quotes in Excel formulas?Excel to CSV with UTF8 encodingShortcut to Apply a Formula to an Entire Column in ExcelSum multiple SUMIFS in Excel?How to use Sumif in Google sheets with many ranges?Excel SUMIF add Multiple CriteriaExcel Sumifs with multiple criteria with ORMultiple Sumrange in SUMIFS excel
What's the point of deactivating Num Lock on login screens?
Blender 2.8 I can't see vertices, edges or faces in edit mode
If a Gelatinous Cube takes up the entire space of a Pit Trap, what happens when a creature falls into the trap but succeeds on the saving throw?
I'm flying to France today and my passport expires in less than 2 months
Is delete *p an alternative to delete [] p?
Etiquette around loan refinance - decision is going to cost first broker a lot of money
Infinite Abelian subgroup of infinite non Abelian group example
Fully-Firstable Anagram Sets
Why can't we play rap on piano?
What is the word for reserving something for yourself before others do?
Could gravitational lensing be used to protect a spaceship from a laser?
Is it unprofessional to ask if a job posting on GlassDoor is real?
Were any external disk drives stacked vertically?
Forgetting the musical notes while performing in concert
Doing something right before you need it - expression for this?
Did Shadowfax go to Valinor?
How to take photos in burst mode, without vibration?
I would say: "You are another teacher", but she is a woman and I am a man
1960's book about a plague that kills all white people
Is it canonical bit space?
Combinations of multiple lists
A reference to a well-known characterization of scattered compact spaces
Arrow those variables!
AES: Why is it a good practice to use only the first 16bytes of a hash for encryption?
EXCEL: SUMIFS with multiple identifiers
How to create Excel (.XLS and .XLSX) file in C# without installing Ms Office?How do I properly clean up Excel interop objects?How to create strings containing double quotes in Excel formulas?Excel to CSV with UTF8 encodingShortcut to Apply a Formula to an Entire Column in ExcelSum multiple SUMIFS in Excel?How to use Sumif in Google sheets with many ranges?Excel SUMIF add Multiple CriteriaExcel Sumifs with multiple criteria with ORMultiple Sumrange in SUMIFS excel
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty height:90px;width:728px;box-sizing:border-box;
I have the following situation in excel on a much bigger scale, but for the purposes of this question, here is what it looks like:
Identifier Merged Identifier Person A Person B Person C
Code1 MCode1 $amount $amount $amount
Code2 MCode1 $amount $amount $amount
Code3 MCode3 $amount $amount $amount
Essentially, I have individuals with dollars associated to certain codes. Some codes have dollar amounts small enough such that they need to be merged into one dollar amount. I created the merged identifier column to dictate which codes align together. When a code is not merged, the identifier and merged identifiers are equal (Code3 = MCode3).
The challenge I am having is that I need to go through a huge database at a Code level, determine if that code is a part of a merged code, and then sum the dollar amounts of the entire merged code for that particular person. At first I tried something like this:
SUMIFS(C2:E4, B2:B4, B2, A2:4, A2)
but the columns and rows don't match so I get the #VAL error. I'm a little stuck on this one, so would love some help.
Cheers,
G
excel excel-formula sum sumifs
asked Mar 8 at 23:31
GandaGanda
1201219
add a comment |
I have the following situation in excel on a much bigger scale, but for the purposes of this question, here is what it looks like:
Identifier Merged Identifier Person A Person B Person C
Code1 MCode1 $amount $amount $amount
Code2 MCode1 $amount $amount $amount
Code3 MCode3 $amount $amount $amount
Essentially, I have individuals with dollars associated to certain codes. Some codes have dollar amounts small enough such that they need to be merged into one dollar amount. I created the merged identifier column to dictate which codes align together. When a code is not merged, the identifier and merged identifiers are equal (Code3 = MCode3).
The challenge I am having is that I need to go through a huge database at a Code level, determine if that code is a part of a merged code, and then sum the dollar amounts of the entire merged code for that particular person. At first I tried something like this:
SUMIFS(C2:E4, B2:B4, B2, A2:4, A2)
but the columns and rows don't match so I get the #VAL error. I'm a little stuck on this one, so would love some help.
Cheers,
G
excel excel-formula sum sumifs
asked Mar 8 at 23:31
GandaGanda
1201219
I'm not sure if I got the problem right: The results should be 1 Sum per person, that sum all amounts in the column titled as that person, in whichs rows the MCode = Code?
– blub
Mar 9 at 10:01
add a comment |
I have the following situation in excel on a much bigger scale, but for the purposes of this question, here is what it looks like:
Identifier Merged Identifier Person A Person B Person C
Code1 MCode1 $amount $amount $amount
Code2 MCode1 $amount $amount $amount
Code3 MCode3 $amount $amount $amount
Essentially, I have individuals with dollars associated to certain codes. Some codes have dollar amounts small enough such that they need to be merged into one dollar amount. I created the merged identifier column to dictate which codes align together. When a code is not merged, the identifier and merged identifiers are equal (Code3 = MCode3).
The challenge I am having is that I need to go through a huge database at a Code level, determine if that code is a part of a merged code, and then sum the dollar amounts of the entire merged code for that particular person. At first I tried something like this:
SUMIFS(C2:E4, B2:B4, B2, A2:4, A2)
but the columns and rows don't match so I get the #VAL error. I'm a little stuck on this one, so would love some help.
Cheers,
G
excel excel-formula sum sumifs
asked Mar 8 at 23:31
GandaGanda
1201219
I have the following situation in excel on a much bigger scale, but for the purposes of this question, here is what it looks like:
Identifier Merged Identifier Person A Person B Person C
Code1 MCode1 $amount $amount $amount
Code2 MCode1 $amount $amount $amount
Code3 MCode3 $amount $amount $amount
Essentially, I have individuals with dollars associated to certain codes. Some codes have dollar amounts small enough such that they need to be merged into one dollar amount. I created the merged identifier column to dictate which codes align together. When a code is not merged, the identifier and merged identifiers are equal (Code3 = MCode3).
The challenge I am having is that I need to go through a huge database at a Code level, determine if that code is a part of a merged code, and then sum the dollar amounts of the entire merged code for that particular person. At first I tried something like this:
SUMIFS(C2:E4, B2:B4, B2, A2:4, A2)
but the columns and rows don't match so I get the #VAL error. I'm a little stuck on this one, so would love some help.
Cheers,
G
excel excel-formula sum sumifs
excel excel-formula sum sumifs
asked Mar 8 at 23:31
GandaGanda
1201219
asked Mar 8 at 23:31
GandaGanda
1201219
asked Mar 8 at 23:31
GandaGanda
1201219
asked Mar 8 at 23:31
GandaGanda
1201219
asked Mar 8 at 23:31
GandaGanda
1201219
1201219
I'm not sure if I got the problem right: The results should be 1 Sum per person, that sum all amounts in the column titled as that person, in whichs rows the MCode = Code?
– blub
Mar 9 at 10:01
add a comment |
I'm not sure if I got the problem right: The results should be 1 Sum per person, that sum all amounts in the column titled as that person, in whichs rows the MCode = Code?
– blub
Mar 9 at 10:01
I'm not sure if I got the problem right: The results should be 1 Sum per person, that sum all amounts in the column titled as that person, in whichs rows the MCode = Code?
– blub
Mar 9 at 10:01
I'm not sure if I got the problem right: The results should be 1 Sum per person, that sum all amounts in the column titled as that person, in whichs rows the MCode = Code?
– blub
Mar 9 at 10:01
add a comment |
2 Answers
2
active
oldest
votes
Take a step back to an old style array formula.
=SUM(IF(A2:A4=F9,IF(B2:B4=G9,C2:E4)))
answered Mar 10 at 3:20
user11174618user11174618
2973
add a comment |
Sumproduct is a very interesting function. Normally you can multiply arrays and get the total. For example (1,2,3) x (2,3,4) = 2+6+12 = 20
But the part that can help you is, instead of giving it plain values, you can multiply an array of values with an array or True / Falses. Your 1st and 2nd columns will be converted to 1s and 0s (1 if the case is true, 0 if it's false)
https://imgur.com/V5GExeB.jpg
=SUMPRODUCT(--(($A$2:$A$4)=$A$7),--(($B$2:$B$4)=$B$7),F$2:F$4)
what this becomes is
(1,0,0) x (1,1,0) x (6,15,24) = 6
answered Mar 10 at 3:40
Baris TasdelenBaris Tasdelen
1665
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%2f55072419%2fexcel-sumifs-with-multiple-identifiers%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
Take a step back to an old style array formula.
=SUM(IF(A2:A4=F9,IF(B2:B4=G9,C2:E4)))
answered Mar 10 at 3:20
user11174618user11174618
2973
add a comment |
Take a step back to an old style array formula.
=SUM(IF(A2:A4=F9,IF(B2:B4=G9,C2:E4)))
answered Mar 10 at 3:20
user11174618user11174618
2973
add a comment |
Take a step back to an old style array formula.
=SUM(IF(A2:A4=F9,IF(B2:B4=G9,C2:E4)))
answered Mar 10 at 3:20
user11174618user11174618
2973
Take a step back to an old style array formula.
=SUM(IF(A2:A4=F9,IF(B2:B4=G9,C2:E4)))
answered Mar 10 at 3:20
user11174618user11174618
2973
answered Mar 10 at 3:20
user11174618user11174618
2973
answered Mar 10 at 3:20
user11174618user11174618
2973
answered Mar 10 at 3:20
user11174618user11174618
2973
2973
add a comment |
add a comment |
Sumproduct is a very interesting function. Normally you can multiply arrays and get the total. For example (1,2,3) x (2,3,4) = 2+6+12 = 20
But the part that can help you is, instead of giving it plain values, you can multiply an array of values with an array or True / Falses. Your 1st and 2nd columns will be converted to 1s and 0s (1 if the case is true, 0 if it's false)
https://imgur.com/V5GExeB.jpg
=SUMPRODUCT(--(($A$2:$A$4)=$A$7),--(($B$2:$B$4)=$B$7),F$2:F$4)
what this becomes is
(1,0,0) x (1,1,0) x (6,15,24) = 6
answered Mar 10 at 3:40
Baris TasdelenBaris Tasdelen
1665
add a comment |
Sumproduct is a very interesting function. Normally you can multiply arrays and get the total. For example (1,2,3) x (2,3,4) = 2+6+12 = 20
But the part that can help you is, instead of giving it plain values, you can multiply an array of values with an array or True / Falses. Your 1st and 2nd columns will be converted to 1s and 0s (1 if the case is true, 0 if it's false)
https://imgur.com/V5GExeB.jpg
=SUMPRODUCT(--(($A$2:$A$4)=$A$7),--(($B$2:$B$4)=$B$7),F$2:F$4)
what this becomes is
(1,0,0) x (1,1,0) x (6,15,24) = 6
answered Mar 10 at 3:40
Baris TasdelenBaris Tasdelen
1665
add a comment |
Sumproduct is a very interesting function. Normally you can multiply arrays and get the total. For example (1,2,3) x (2,3,4) = 2+6+12 = 20
But the part that can help you is, instead of giving it plain values, you can multiply an array of values with an array or True / Falses. Your 1st and 2nd columns will be converted to 1s and 0s (1 if the case is true, 0 if it's false)
https://imgur.com/V5GExeB.jpg
=SUMPRODUCT(--(($A$2:$A$4)=$A$7),--(($B$2:$B$4)=$B$7),F$2:F$4)
what this becomes is
(1,0,0) x (1,1,0) x (6,15,24) = 6
answered Mar 10 at 3:40
Baris TasdelenBaris Tasdelen
1665
Sumproduct is a very interesting function. Normally you can multiply arrays and get the total. For example (1,2,3) x (2,3,4) = 2+6+12 = 20
But the part that can help you is, instead of giving it plain values, you can multiply an array of values with an array or True / Falses. Your 1st and 2nd columns will be converted to 1s and 0s (1 if the case is true, 0 if it's false)
https://imgur.com/V5GExeB.jpg
=SUMPRODUCT(--(($A$2:$A$4)=$A$7),--(($B$2:$B$4)=$B$7),F$2:F$4)
what this becomes is
(1,0,0) x (1,1,0) x (6,15,24) = 6
answered Mar 10 at 3:40
Baris TasdelenBaris Tasdelen
1665
answered Mar 10 at 3:40
Baris TasdelenBaris Tasdelen
1665
answered Mar 10 at 3:40
Baris TasdelenBaris Tasdelen
1665
answered Mar 10 at 3:40
Baris TasdelenBaris Tasdelen
1665
1665
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%2f55072419%2fexcel-sumifs-with-multiple-identifiers%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
I'm not sure if I got the problem right: The results should be 1 Sum per person, that sum all amounts in the column titled as that person, in whichs rows the MCode = Code?
– blub
Mar 9 at 10:01