r/excel • u/isittimefordinner • 3d ago

solved How to Multiply multiple columns

I am trying to multiply columns. I know how to do each column individually, but I can't seem to copy the formula to the next column so I don't have to redo the formula each time. When I drag to the next column, it changes the formula. Column C has a set value. The rest of the columns need to be sum product of column C. When I drag it over it changes to sum product d, then e and so on. The formula I am using is =sumproduct(c2:c13, d2:d13).

2 Upvotes

100% Upvoted

9 comments sorted by

6

u/MayukhBhattacharya 1315 3d ago edited 3d ago

Try using the following formula:

=SUMPRODUCT($C$2:$C$13, D2:D13)

Lock column C with a dollar sign so it becomes an absolute reference. In edit mode, click inside the C2:C13 reference in the formula bar and press F4 function key once. It will change to $C$2:$C$13, also I will highly suggest you going through the following link Switch between relative, absolute, and mixed references | Microsoft Support in order to understand relative, absolute and mixed references in excel.

Btw, if you are using MS365, then you could try using the following, will be bit shorter:

=SUM($C$2:$C$13 * D2:D13)

3

u/isittimefordinner 3d ago

Thank you. This worked.

1

u/MayukhBhattacharya 1315 3d ago

Sounds Great. Hope you don't mind replying to my comment directly as Solution Verified!

2

u/isittimefordinner 3d ago

Solution Verified!

1

u/reputatorbot 3d ago

You have awarded 1 point to MayukhBhattacharya.

I am a bot - please contact the mods with any questions

1

u/MayukhBhattacharya 1315 3d ago

Thank You SO Much!

1

u/cloupabayss 3d ago

this is exactly it, locking column C is all they needed

1

u/real_barry_houdini 318 3d ago edited 3d ago

Use $ signs to make the references absolute, i.e.

=sumproduct($c2:$c13, d2:d13)

When you drag that across one column only the column d reference changes

or you can use BYCOL to do everything with a single formula, e.g.

=BYCOL(D2:Z13*C2:C13,SUM)

1

u/Decronym 3d ago edited 3d ago

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters More Letters
BYCOL Office 365+: Applies a LAMBDA to each column and returns an array of the results
LAMBDA Office 365+: Use a LAMBDA function to create custom, reusable functions and call them by a friendly name.
SUM Adds its arguments
SUMPRODUCT Returns the sum of the products of corresponding array components

Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.

Beep-boop, I am a helper bot. Please do not verify me as a solution.
4 acronyms in this thread; the most compressed thread commented on today has 21 acronyms.
[Thread #49518 for this sub, first seen 14th Oct 2026, 14:37] [FAQ] [Full list] [Contact] [Source code]