CODEWITHSUNDEEP
excel-formula
user2029890
user2029890

Reputation: 2713

Excel sum values if exists in another column without vlookup

This is my data

A    B    C 
a    a   100    
b    r   200
c    m   300
d    b   400 
e    d   500

I want to sum C if B exists in A.

vlookup won't work because I cannot add a column (for reasons that cannot be changed). This must all be done from one cell. I cannot drag down a formula on any column.

I was thinking something like sumifs(C:C,A:A,{if value for A exists in B:B}

I'm not too familiar with arrays in Excel, but there must be a way to specify 

if value for A exists in B:B

Thanks!

Upvotes: 0

Views: 893

Answers (2)

Scott Craner
Scott Craner

Reputation: 152505

A single formula using SUMPRODUCT:

=SUMPRODUCT(--ISNUMBER(MATCH(B1:B5,A:A,0)),C1:C5)

with SUMPRODUCT one should limit the size of the referenced data to that of the actual data to limit the unneeded iterations.

enter image description here

Upvotes: 2

Gary's Student
Gary's Student

Reputation: 96753

Here is an easy way. In D1 enter:

=IF(ISNA(MATCH(B1,A:A,0)),0,1)

and copy down. (column D marks the rows to add)

Then in another cell:

=SUMPRODUCT(C:C*D:D)

or

=SUMIFS(C:C,D:D,1)

enter image description here

Upvotes: 2

Related Questions