Aug 01 2019 10:56 AM - edited Aug 01 2019 11:15 AM
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.
Aug 01 2019 11:11 AM
Aug 01 2019 11:17 AM
File attached @Twifoo. Thanks!
Aug 01 2019 11:19 AM
Aug 01 2019 11:22 AM
It's attached to my original question @Twifoo. Here it is in this reply as well. Kind regards.
Aug 01 2019 01:30 PM
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
Aug 01 2019 02:04 PM - edited Aug 01 2019 02:05 PM
Aug 01 2019 10:04 PM
SolutionThe 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))
Aug 06 2019 12:52 PM
Thank you so much @Twifoo! It works like a charm in my Master Spreadsheet. You're a lifesaver.
Aug 06 2019 01:19 PM
Aug 01 2019 10:04 PM
SolutionThe 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))