Search code examples
libreoffice-calc

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

Solution

  • 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.