Excel: Sum all values in Column B for each ID in Column A, and create a new column with all these results

Question

So I don't know if I'm framing this correctly. I have a database with thousands of rows. Column A is a code that identifies a specific product, and Column B has a (0-no;1-yes) value. I need each row in Column C to have the sum of all values in Column B, for each specific product in Column A.

In the example below, there are three products, and Column C is how the output should look like. All rows with X in Column A sum the values Column B (only from said rows) and the result printed on all of the same rows in Column C. Every row with X (Column A) should have a 5 (Column C), which is the sum of all 1s in Column B (only on rows with an X).

Column A	Column B	Column C
X	1	5
Y	0	2
Y	1	2
Z	0	1
X	1	5
X	1	5
Z	1	1
X	1	5
Z	0	1
X	1	5
X	0	5
Z	0	1
Y	1	2

Keep in mind that there are thousands of products with different and complex IDs (Column A). I thought of creating a Dynamic Table that gives me the result (sum of B for said values) in a different sheet and then merging both based on Column A values, but I'm trying to do this in one single step with a code in Column C.

Answer 1

There are few ways of doing this, perhaps for readability one can use the following formulas:

---

=LET(α, A2:B14, δ, TAKE(α,,1), HSTACK(α,SUMIFS(TAKE(α,,-1),δ,δ)))

---

Or Using SUMIF() may be:

=LET(α, A2:B14, δ, TAKE(α,,1), HSTACK(α,SUMIF(δ,δ,TAKE(α,,-1))))

---

With out returning the whole array and only for column C

=SUMIF(A2:A14,A2:A14,B2:B14)

---

Another alternative is to use MMULT()

=MMULT(N(A2:A14=TOROW(A2:A14)),B2:B14)

---

---

Answer 2

Try this formula works on old Excels also:

=SUMPRODUCT(IF((A$2:A$14=A2)*(B$2:B$14=1),1,0))