Using INDEX MATCH function to multiply and sum rows-CodePudding

I've the following table:

In cell A7 I define the index of column 1 I want to use for my computation, in this case A. For every row with index A I want to multiple the value for that row of column 2 with column 3. In this example there are two rows with index A, so I want to do the computation for both rows and then add the results. That will look as follows: 5 * 3 9 * 7= 78.

To achieve this, I first tried to write a code that sums all values in column 2 that match a given index. That index is A, so 5 9= 14 is what the output should be. I only get my code to find the first match, so that's row 2 and it will display the value of column 2, so that's 5. This is my code for cell B7:

=SUM(INDEX(B2:B5;MATCH(A7;A2:A5;0)))

Even if I solve this I still don't have what I actually want, but I think it's a start. How do I get what I innitially wanted and have the outcome equal 78?

CodePudding user response：

Using BYROW()

• Formula used in cell B7

=SUM(BYROW(FILTER(Table12[[Column 2]:[Column 3]],A7=Table12[Column 1]),LAMBDA(m,PRODUCT(m))))

If not using Structured References then

=SUM(BYROW(FILTER(B2:C5,A7=A2:A5),LAMBDA(m,PRODUCT(m))))

CodePudding user response：

Type in this formula:

=SUM((A2:A5=A7)*(B2:B5)*(C2:C5))

Then Ctrl-Shift-Enter

This converts it into an array formula, which you can identify because there will be braces around it:

{=SUM((A2:A5=A7)*(B2:B5)*(C2:C5))}