Multiply and Sum each other columns

[luis_marques]

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

 

[luis_marques]

Hi. Any help? Please

 

[p45cal]

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]

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.

 

[p45cal]

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]

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.

 

[p45cal]

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?

 

[luis_marques]

Thanks. That is what i need.

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

 

[p45cal]

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

 

[luis_marques]

Perfect. Many thanks for your help.

 

[bosco_yip]

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 :

=IFERROR(SUMPRODUCT(FILTERXML("<a><b>"&SUBSTITUTE(SUBSTITUTE($A2,"quantidade"":","<r/>"),",""","</b><b>")&"</b></a>","//b[r]")*FILTERXML("<a><b>"&SUBSTITUTE(SUBSTITUTE($A2,"preco_custo"":","<r/>"),",""","</b><b>")&"</b></a>","//b[r]")),"")

 

[luis_marques]

Many thanks, Bosco.

 

[Excel Wizard]

Please try

=SUMPRODUCT(FILTERXML("<x>"&SUBSTITUTE(SUBSTITUTE(A1,"dade"":","<m>"),",""preco_cheio","</m>")&"</x>","//m"),FILTERXML("<x>"&SUBSTITUTE(SUBSTITUTE(A1,"custo"":","<m>"),",""produto","</m>")&"</x>","//m"))

or Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    AddedTotal = Table.AddColumn(Source, "Total", each List.Sum(Table.AddColumn(Table.FromRecords(Json.Document([Column1])) ,"T",each [quantidade]*[preco_custo])[T]) )
in
    AddedTotal

 

[luis_marques]

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

 

[Peter Bartholomew]

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

= 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

= EXTRACTVALUE(record, "preco_custo")
= EXTRACTVALUE(record, "quantidade")

or, if the values were required as an array,

EXTRACTVALUE(record, {"quantidade";"preco_custo"})

I don't know about anyone else but I have trouble even thinking of this as a spreadsheet.
It may share the same familiar functions but it doesn't look like end-user computing.