1

I've the following table:

enter image description here

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?

Mayukh Bhattacharya
  • 12,541
  • 5
  • 21
  • 32
Murcie
  • 87
  • 5

2 Answers2

1

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))}

Jim Becker
  • 222
  • 1
  • 4
0

Using BYROW()

enter image description here

• 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))))

Or, use the incredible & versatile SUMPRODUCT() Function

enter image description here

• Formula used in cell C7

=SUMPRODUCT((A7=A2:A5)*(B2:B5)*(C2:C5))
Mayukh Bhattacharya
  • 12,541
  • 5
  • 21
  • 32