enter image description hereI have an excel file with at least 30,000 different ranges, of different size that are not formatted as tables, just data.

I have column F with quantities, column G with unit price and col J with totals cost (FxG).

I am tasked with summing each range in column J and placing the product under column G.

Any advice how to avoid doing 30,000 manual sums between the data?

CodePudding user response：

Are the ranges to sum all vectors as opposed to matrices?

If that's the case, couldn't you just set the range equal to the longest vector, since blank cells are essentially equal to zero when summing?

CodePudding user response：

I not really get your question, but hope this could help.

Do you want this action?

If yes, see this jpg

I would like to recommend you to change the data arrangement so it will be easy to manipulate or analysis etc. You might create a table data which contain with all purchasing information and another table data to summarize the information, Excel Pivot table will give you full control of your data.

CodePudding user response：

In excel select all data, click on tab data > Outline> Subtotal

from dialogue box select the following options

at each change in: "Customer Name" use function: "sum" Add subtotal to: "Total"

Now apply filter on your data in "Customer name" column and search for "Total"

in the filtered dataset set each visible cell of "J" column equal to "G"

remove filter and paste data of "J" column as value