I have the following table:
| OrderNumber | Value |
|---|---|
| 123 | 2 |
| 123 | 3 |
| 333 | 5 |
| 333 | 6 |
| 555 | 8 |
| 555 | 9 |
My goal is to sum all OrderNumbers with the same values (e.g. for OrderNumber 123 the sum should be 5) and output the result in a new row.
The output should be like this:
| OrderNumber | Value | Result |
|---|---|---|
| 123 | 2 | 5 |
| 123 | 3 | 5 |
| 333 | 5 | 11 |
| 333 | 6 | 11 |
| 555 | 8 | 17 |
| 555 | 9 | 17 |
I've seen some formulas beginning with =SUM(A2:A6;A2;B2:B6). Important to me is that the searching criteria must be dynamically because my table has about 1k rows.
Do you have any references or suggestions?
CodePudding user response：
You need SUMIF() function.
=SUMIF($A$2:$A$7,A2,$B$2:$B$7)
If you are a Microsoft 365 user then can try BYROW() for one go.
=BYROW(A2:A7,LAMBDA(x,SUMIF(A2:A7,x,B2:B7)))
CodePudding user response：
This is the exact reason why the "Subtotals" feature has been invented:
