SOLVED
Home

Excel: SUM values in a column if matching data in multiple other columns

%3CLINGO-SUB%20id%3D%22lingo-sub-784433%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%3A%20SUM%20values%20in%20a%20column%20if%20matching%20data%20in%20multiple%20other%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-784433%22%20slang%3D%22en-US%22%3EPlease%20attach%20your%20sample%20file.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-784397%22%20slang%3D%22en-US%22%3EExcel%3A%20SUM%20values%20in%20a%20column%20if%20matching%20data%20in%20multiple%20other%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-784397%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20like%20to%20sum%20data%20in%20column%20L%20(genetic%20marker%20length)%20if%20the%20following%20conditions%20are%20met%3C%2FP%3E%3CP%3E1.%20Matching%20value%20in%20column%20D%20(sample%20ID)%3C%2FP%3E%3CP%3Eand%3C%2FP%3E%3CP%3E2.%20Matching%20value%20in%20column%20K%20(genetic%20marker)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EColumn%20D%20contains%20a%20wide%20variety%20of%20numeric%20sample%2Fsubject%20IDs%20and%20column%20K%20contains%20a%20variety%20of%20genetic%20markers.%20Column%20L%20is%20the%20length%20of%20those%20markers.%20Essentially%20I%20want%20excel%20to%20add%20the%20lengths%20for%20all%20identical%20markers%20for%20each%20sample%20ID.%20Extra%20bonus%20if%20I%20can%20also%20display%20the%20value%20in%20column%20L%20when%20those%20criteria%20are%20not%20met%2C%20so%20my%20data%20is%20still%20all%20in%20a%20single%20column.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20attached%20an%20abbreviated%20file%20containing%20mock%20data%20in%20my%20columns%20of%20interest%20for%20this%20question.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-784397%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-784450%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%3A%20SUM%20values%20in%20a%20column%20if%20matching%20data%20in%20multiple%20other%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-784450%22%20slang%3D%22en-US%22%3E%3CP%3EFile%20attached%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E.%20Thanks!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-784464%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%3A%20SUM%20values%20in%20a%20column%20if%20matching%20data%20in%20multiple%20other%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-784464%22%20slang%3D%22en-US%22%3ENo%20file%20was%20attached.%20Please%20try%20again.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-784478%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%3A%20SUM%20values%20in%20a%20column%20if%20matching%20data%20in%20multiple%20other%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-784478%22%20slang%3D%22en-US%22%3E%3CP%3EIt's%20attached%20to%20my%20original%20question%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E.%20Here%20it%20is%20in%20this%20reply%20as%20well.%20Kind%20regards.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-784705%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%3A%20SUM%20values%20in%20a%20column%20if%20matching%20data%20in%20multiple%20other%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-784705%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F385901%22%20target%3D%22_blank%22%3E%40mandyb385%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%3C%2FP%3E%3CP%3EThe%20attached%20file%20does%20not%20match%20your%20question%20and%20criteria%20(Nothing%20in%20column%20L%2C%20K%2C...)%3CBR%20%2F%3EWhen%20you%20provide%20a%20clear%20sample%20file%20you%20get%20more%20replies.%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-784779%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%3A%20SUM%20values%20in%20a%20column%20if%20matching%20data%20in%20multiple%20other%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-784779%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F385901%22%20target%3D%22_blank%22%3E%40mandyb385%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20based%20on%20a%20table%20and%20structured%20references%20and%20SUMIFS.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-785086%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%3A%20SUM%20values%20in%20a%20column%20if%20matching%20data%20in%20multiple%20other%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-785086%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F385901%22%20target%3D%22_blank%22%3E%40mandyb385%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20formula%20in%20E2%20of%20the%20attached%20file%20is%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DIF(COUNTIFS(A2%3AA%2421%2CA2%2CB2%3AB%2421%2CB2)%26gt%3B1%2C%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3E%22%22%2C%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3ESUMIFS(C%242%3AC2%2CA%242%3AA2%2CA2%2CB%242%3AB2%2CB2))%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-790810%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%3A%20SUM%20values%20in%20a%20column%20if%20matching%20data%20in%20multiple%20other%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-790810%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%20so%20much%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E!%20It%20works%20like%20a%20charm%20in%20my%20Master%20Spreadsheet.%20You're%20a%20lifesaver.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-790838%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%3A%20SUM%20values%20in%20a%20column%20if%20matching%20data%20in%20multiple%20other%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-790838%22%20slang%3D%22en-US%22%3EYour%20thanks%20sound%20a%20charm%2C%20too!%3C%2FLINGO-BODY%3E
mandyb385
New Contributor

Hello,

 

I would like to sum data in column L (genetic marker length) if the following conditions are met

1. Matching value in column D (sample ID)

and

2. Matching value in column K (genetic marker)

 

Column D contains a wide variety of numeric sample/subject IDs and column K contains a variety of genetic markers. Column L is the length of those markers. Essentially I want excel to add the lengths for all identical markers for each sample ID. Extra bonus if I can also display the value in column L when those criteria are not met, so my data is still all in a single column.

 

I attached an abbreviated file containing mock data in my columns of interest for this question.

9 Replies
Please attach your sample file.
No file was attached. Please try again.

It's attached to my original question @Twifoo. Here it is in this reply as well. Kind regards.

@mandyb385 

Hi

The attached file does not match your question and criteria (Nothing in column L, K,...)
When you provide a clear sample file you get more replies.

Thanks

@mandyb385 

This is based on a table and structured references and SUMIFS.

 

Solution

@mandyb385 

The formula in E2 of the attached file is: 

=IF(COUNTIFS(A2:A$21,A2,B2:B$21,B2)>1,
"",
SUMIFS(C$2:C2,A$2:A2,A2,B$2:B2,B2))

Thank you so much @Twifoo! It works like a charm in my Master Spreadsheet. You're a lifesaver.

Your thanks sound a charm, too!
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies