CODEWITHSUNDEEP
excelexcel-formulaexcel-2013
whytheq
whytheq

Reputation: 35567

Sum one column if number not in another column

The following formula should return 15 in each of the cells E3:E6. It should sum column labeled X but not include any numbers in this sum if they are found in column D.

Note: no additional helper columns are allowed, but the Total cells C8:D8 can be used.

enter image description here

Upvotes: 1

Views: 251

Answers (2)

Mr.Jack1
Mr.Jack1

Reputation: 43

This works:

=DSUM($C$3:$D$6;1;$D$3:$D$6)

Upvotes: 3

Jean-François Corbett
Jean-François Corbett

Reputation: 38520

=SUMPRODUCT(ISNA(MATCH($C$3:$C$6,$D$3:$D$6,0))*$C$3:$C$6)

enter image description here

MATCH returns an #N/A! error when it can't find the lookup value (X) in the lookup array (Y). ISNA() converts matches to FALSE and non-matches (#N/A!) to TRUE. When multiplied using the mathematical operator *, TRUE becomes 1 and FALSE becomes 0. SUMPRODUCT finally sums up the X's that line up with 1's (and not those with 0's).

Upvotes: 3

Related Questions