CODEWITHSUNDEEP
excel
Andrew
Andrew

Reputation: 7778

How to multiply each row and then sum it with the product in the next row etc

I am trying to calculate total for each month based on Score and Number of occuruencies each month:

Category#   Score   Jan-18  Feb-18  Mar-18  Apr-18
category1   10      1       5       1       5
category2   8       2       4       2       4
category3   7       3       3       3       3
category4   6       4       0       4       0
category5   5       0       1       0       1
    TOTAL           71      108     71      108

In the essence, for January I could type the following formula:

=($B$2*C2)+($B$3*C3)+($B$4*C4)+($B$5*C5)+($B$6*C6)

But it is very clumsy, so I am wondering if I could something more elegant and clean

Upvotes: 0

Views: 73

Answers (2)

Foxfire And Burns And Burns
Foxfire And Burns And Burns

Reputation: 11978

Use SUMPRODUCT. It's exactly what you need:

SUMPRODUCT function

I replied your data:

enter image description here

The formula I have used is:

=SUMPRODUCT($B$4:$B$8;C4:C8)

After applying to first column (Jan-18), just drag it to the right, and it should return the right values, as you can see in the image.

Hope this helps!

Upvotes: 2

Scott Craner
Scott Craner

Reputation: 152505

that is what SUMPRODUCT is for

=SUMPRODUCT($B$2:$B$6,C2:C6)

Upvotes: 2

Related Questions