CODEWITHSUNDEEP
libreoffice-calc
user3343073
user3343073

Reputation: 75

LibreOffice formula - find from list and sum the quantity

I have a spredsheet that holds in first colum Part Number and in the second price in $ for this PN( and some other details). The list is aprox 100.000 items long.

Sometimes I get a from the customer a query about the total price for eg. 500 item, some of them in different quantity.

If possible could I create a second sheet in that sheet import the requested PN and their quanitiy and than with a formula get the data from the first sheet1 the total amount.

For eg.

    Sheet 1 
PN  Price
A   234
B   765
C   547
D   24
E   45
F   754

    Sheet 2     
Pn  Quantity    Total
A   1           234
D   3           72
E   2           90
                396

Thanks in advance

Upvotes: 1

Views: 583

Answers (1)

tohuwawohu
tohuwawohu

Reputation: 13608

You can solve this easily using the VLOOKUP() function. It takes an array, searches for a certain value and returns a value from another column of the same row. In your case, you would put the following formula into the cell Sheet2:C2 (first cell below "Total"):

=VLOOKUP(A2; Sheet1!$A$2:$B$7; 2) * B2

(i've added some spaces to improve readability).

The formula explained:

=VLOOKUP(
    A2;               # 1st argument: search criterion: the current PN
    Sheet1!$A$2:$B$7; # 2nd argument: search array (both search criterion and return value!)
    2                 # 3rd argument: index of column with return value.
)
* B2                  # multiply the returned value with quantity

Since the 2nd argument has absolute cell references, you can just drag the formula down on sheet 2 to expand it to the other lines.

I can't tell if this works well with a very large ods file, in this case you may be better off importing that data into a LibreOffice Base SQL database.

Upvotes: 1

Related Questions