# Multiply and Sum each other columns

#### luis_marques

##### Member
Hi,

I have a sheet where each row is an order.
Each order may have since 1 product until n products.
The export file of this data order present a pattern of data that repeats every each 14 columns for each product
Those 14 columns are data of just 1 product of 1 order.
I need to multiply 2 columns of each product and then sum with another multiplication of the next 2 columns of the further product.

In order to facilitate comprehension, i uploaded an example sheet. I put just one row that represent one order. There is a set of 14 columns that repeat in this row. I put just 4 set of 14 columns. Remember that each 14 columns represent one product of this order (row). I need to multiply the 4 th column with 12 th column.
If there is just one product, that is simple like the ordinary formula i put in the outline in the file. But if there are several products in the order, i need to multiply the 4 th with 12 th column for each product and after sum all. The number of products data (each 14 columns in the row) are variable for each order (row)

I need a formula to recognize such pattern and make this calculation.

I hope you can help me. Please dont hesitate to ask if you find explanation confusing.

Best Regards

#### Attachments

• 9.6 KB Views: 14

#### p45cal

##### Well-Known Member
That is a truly awful format for output.
I presume you've imported this data into Excel from a data file of some sort?
If so can you attach a copy of that file?

#### luis_marques

##### Member
P45cal, it is a weird format. More weird is that those columns are extracted from just one cell. I use a macro to extract information. See attached.

I would appreciate a lot if you could help to solve this.

#### Attachments

• 16.6 KB Views: 7
Last edited:

#### p45cal

##### Well-Known Member
Are you really receiving this information as an Excel file (SAMPLE.xlsx)?!
Where does it come from? (It looks JSON-like)
It's very likely that this data can be imported (a) more robustly and (b) in a more friendly way (you're running a version of Excel that will handle this).
I'd like to see that data in its original format.

#### luis_marques

##### Member
Hi,

Our ecommerce is hosted in a cloud plataform. The only format they offer to export order data is attached. I kept just 3 original rows for security reason. With this file in hand i use the macro in the SAMPLE.xlsx to split single cell data (Column AT) in several columns in a different workbook.

Now i need to work with some data to calculate some costs. That is the reason of this thread.

Best regards.

#### Attachments

• 10.2 KB Views: 6

#### p45cal

##### Well-Known Member
There's no macro in an xlsx file. No matter.
In the attached is a pivot table at cell A8.
Is this the sort of thing you're after?

#### Attachments

• 188 KB Views: 4

#### luis_marques

##### Member
Thanks. That is what i need.

How do i inform pivot table to split data in the AT column?

#### p45cal

##### Well-Known Member

On the right you will find the steps where you can select them and see what happens.

#### luis_marques

##### Member
Perfect. Many thanks for your help.

#### bosco_yip

##### Excel Ninja
P45cal, it is a weird format. More weird is that those columns are extracted from just one cell. I use a macro to extract information. See attached.

I would appreciate a lot if you could help to solve this.
In the attached file is a formula solution based on your file extracted data in one cell.

In B2 formula copied down :

#### Attachments

• 25.4 KB Views: 6

#### luis_marques

##### Member
Many thanks, Bosco.

#### Excel Wizard

##### Member

or Power Query

Code:
``````let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
in

#### Attachments

• 42.1 KB Views: 3

#### luis_marques

##### Member
Excel Wizard, thanks for both solutions in the Sample.xlsx and also for Power Query code (straightforward approach). Best Regards.

#### Peter Bartholomew

##### Well-Known Member
I have Excel365 (beta channel) and took a look at the new functionality including LAMBDA functions and, slightly older, LET.
It may be years before I really get a feel for how the new functionality is best deployed!
First I wrote a formula using LET to return the "quantidade" from a particular cell. Then I wrapped it with a LAMBDA function to allow me to give the cell containing the compound string and the property to extract as parameters to the Lambda function EXTRACTVALUE.
The Lambda function is defined by
Code:
``````= LAMBDA(string0,fieldName,
LET(
string, SUBSTITUTE(string0, "}]", ","),
label, SEARCH(fieldName, string),
start, label+2+LEN(fieldName),
end, SEARCH(",", string, start),
VALUE( MID(string, start, end-start) ) )
)``````
The price and quantity are then returned by the formula
Code:
``````= EXTRACTVALUE(record, "preco_custo")
``EXTRACTVALUE(record, {"quantidade";"preco_custo"})``