Performing operations on groups of rows in excel

Question

I have the following set of data (this is a dummy, the actual data is a whole bulk file) in excel:

A a1 2 5
A a2 1 4
A a3 0 5
A a4 2 3
A s5 2 4
B b1 5 5
B b1 4 3
B b3 4 1
C c1 2 2
C c2 3 5
C c3 1 4
C c4 0 2

Assuming that the columns are from A to D in an excel sheet, I want to perform the operation ((C*D)/sum(C)) for groups of rows i.e. the formula to be separately applicable for all A data, then all B data and then all C data separately.

I understand we have to use loops with If else statements but still abit confused how to go about it. Please help.

Answer 1

Use SUMIFS()

=(C1*D1)/SUMIFS(C:C,A:A,A1)

---

If instead you want to sum the whole in one formula, use SUMPRODUCT()

=SUMPRODUCT(C1:C12,D1:D12,1/SUMIFS(C:C,A:A,A1:A12))