How to get value of a cell based on key on neighboring cell in Excel?

Question

Input:

I have the following data;

	C	D
1	$1	ABC
2	$2	ABC
3	$3	DEF
4	$4	ABC

I want to create another table in the same sheet where I want to add the values in Column C based on the keys in Column D.

Output:

	G	H
1	ABC	$7
2	DEF	$3

Here $7 is the sum of values corresponding to ABC and $3 is the sum of values corresponding to DEF.

So I need something like "= Sum of values in Column C corresponding to Key in G1 found in Column D" in H1 and so on...

What's the best way to achieve this instead of adding the values manually?

Thank you for your help.

Answer 1

I solved it using SUMIF.

The formula for H1 would be:

=SUMIF(D:D,G1,C:C)

Alternatively, you can also use Pivot table to group and sum the values of column C based on column D.

Answer 2

Your sample data:

In cell C10, use this formula:

=SUMPRODUCT(($C$2:$C$5=B10)*$B$2:$B$5). Drag down to fill other cells in the column:

You'll get the sum of all matched values.