Summing values that match across two arrays

Question

I have an Excel sheet setup as follows:

My goal is to create a matrix of the reference column and in that matrix, I want to show the sum of the values where the ID is present in both references. For example, in this data table, the ID's "A" and "E" are present in reference 1 and reference 2 (see orange highlights). Hence, in the matrix output, at the intersection of reference 1 and 2, I would want to show the sum of the values of ID's A and E. The actual dataset is larger.

Can anyone help with an Excel formula that would allow this.

Currently I am using lookups to identify the ranges of the references (i.e. the range of cells for reference 1 and the range of cells for reference 2). My plan was to then try to sum_duplicates(ref_1_range, ref_2_range) if such a formula existed.

Any thoughts would be greatly appreciated!

Answer 1

I'm not sure if this is the best way to do it - this is one approach, for H2 looking up pairs like 1A,1E,1E,1D,1E,1A,1G in the first two columns combined (1A,1B,1E,1D,2E,2A,2G) then narrowing down the matches to the ones with a reference of 2:

=SUMPRODUCT($C$2:$C$8*ISNUMBER(MATCH($F2&$B$2:$B$8,$A$2:$A$8&$B$2:$B$8,0))*($A$2:$A$8=H$1))

I have filled in the other elements of the 2 X 2 table as a check, and changed C4 to 3 to make the four totals different.

EDIT

=SUMPRODUCT($C$2:$C$8*(COUNTIFS($A$2:$A$8,$F2,$B$2:$B$8,$B$2:$B$8)>0)*($A$2:$A$8=H$1))

is a bit better.

Answer 2

You could use a helper column that checks whether that value is in the other reference, then sum on whether that column = TRUE

For example:

• The formula in D2 is: =COUNTIFS(B:B,B2,A:A,"<>"&A2)>0
• The formula in G2 is: =SUMIFS(C:C,A:A,F2)
• The formula in H2 & G3 is: =SUMIFS(C:C,D:D,TRUE)
• The formula in H3 is: =SUMIFS(C:C,A:A,F3)