SOLVED

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

Copper 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.

 

best response confirmed by mandyb385 (Copper Contributor)
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!
1 best response

Accepted Solutions
best response confirmed by mandyb385 (Copper Contributor)
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))

View solution in original post