Search code examples
excelsumifs

How to multiply multiple columns with the same ID in excel

I have a dataframe as follows:

ID   ColB    ColC   ColD
102     2       1      2
103     2       3      4
104     3       3      2
104     6       7      5
105    33       3      3
105    22       2      2
106     1       3      2

I would like to populate a new column E which finds every time an ID appears and sums ColC and ColD of this row and multiplies the sum by ColB. All these should be summed up for each time the ID value appears, eventually filling a table of unique ID values.

Using 104 for an example it should look like

= (B4) *(C4+D4) + (B5) *(C5+D5)
= (3)  *(3+2)   + (6)  *(7+5) = 87

I tried a combination of PRODUCT() and SUMIF() however I am not getting the desired result since I am relying on both multiplication and summation of various cells based on the same identifier...

Solution

  • Use SUMPRODUCT:

    =SUMPRODUCT((A2:A8=104)*B2:B8,C2:C8+D2:D8)

    enter image description here