Search code examples
excelindexingmatchsumproduct

Sum all the multiplications of 2 index matches

I'm trying the following:

Through indexing and matching get 2 values from 2 different tabs and multiply them, and then summing up all instances of those multiplied matches. I'm trying to achieve this in 1 step.

To illustrate:

  1. From tab 'Test_Samples' Match all values in column A with values in 'Reference_Dataset' Column A.

  2. Get the corresponding "count" from tab 'Test_Samples' column B and multiply it by the corresponding "gra" from tab 'Reference_Dataset' column J.

  3. Add up all results of these multiplications.

enter image description here

enter image description here

I've tried SUMIF/SUMIFS and SUMPRODUCT and nesting the indexing and matching in there, but it gives either the wrong results or says the formula is not correct.

The way I index and match to get the "gra" value in column J of the tab 'Reference_Dataset' is as follows:

=INDEX(Reference_Dataset!$D:$Z,MATCH($A2,Reference_Dataset!$A:$A,0),MATCH(K$1,Reference_Dataset!$D$1:$Z$1,0))

So if "Apple", "Bee" and "Cinnamon" in column A of both tabs have counts in tab 'Test_Samples' of 19, 1 and 10 respectively and the corresponding "gra" values for "Apple", "Bee" and "Cinnamon" in tab 'Reference_Dataset' are 2, 1, 1 respectively, it should multiply and sum up (19*2 + 1*1 + 10*1) to return a total of 49.

Solution

  • Use an array formula like this:

    =SUMPRODUCT(G1:G5,INDEX(B:B,N(IF({1},MATCH(F1:F5,A:A,0)))))

    Being an array formula it must be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode.

    enter image description here