Insert a sum function after first blank row

[mturnertombow]

Hi all,
I have a lot of worksheets with identical set-up, although varying rows of data. I have a cost in one column, and a count in another. For each column with a count, I want to sum the total cost. That is, for columns D, E, F, I want to multiply the count of each item to it's cost, then get the total. For example, I have this currently:

Please Login or Register  to view this content.

where $C will have my cost. I used this to drag across multiple columns for one sheet.

However, the amount of rows will be increasing or decreasing and I don't want to have to change the formula each time I add or subtract something. I need a macro that can:

1. Look at the number of rows and insert a formula after the first blank row
2. Insert a formula that sums (CostA*QuantityA)+(CostB*QuantityB)+etc for each item in the column
3. Will dynamically update the formula if there are additions or deletions

Please let me know if that's not clear or if you need more specifics. I don't mind re-running the macro if/when a row is changed, so # three on the list isn't high priority.

[Paul]

Hi MTurnertombow,

You can use SUMPRODUCT for this exact application, for example:

=SUMPRODUCT(C:C*D:D)

If you have a lot of values, you may want to reduce the range, but make it greater than anything you'd expect, e.g.

=SUMPRODUCT((C2:C1000)*(D2:D1000))

[mturnertombow]

I don't know if you could have made that any simpler. I never knew how that works but that is an exceedingly handy tool. Thanks!

[mturnertombow]

Hi MTurnertombow,

You can use SUMPRODUCT for this exact application, for example:

=SUMPRODUCT(C:C*D:D)

If you have a lot of values, you may want to reduce the range, but make it greater than anything you'd expect, e.g.

=SUMPRODUCT((C2:C1000)*(D2:D1000))

Does this not work with Vlookup? My column with Cost is

Please Login or Register  to view this content.

and when I use the =SUMPRODUCT(C:C*D:D) it just gives me a 0.


EDIT: Nevermind. It works.