How to Search for a value and sum a related one?

[s.tsiolas]

In column A I have drop down lists (names of different products). In Column B there is an input of how many kg is going to be used. In column A one specific product may be appeared more than one times and the same is happening in other columns. I.E.

A1 = productA , B1= 100
A10 = productA, B10=30
C11 = productA, D11=70
E100= productA, F100=200

What I need is a formula that will search the whole page for product A and then sum the cell with its quantity. In the above example the result I am searching for is 400.

Could somebody please help me with that?

Thanks a lot!

[ken johnson]

This array formula seems to work...
=SUM(IF(A1:I14="Product A";1;0)*OFFSET(A1:I14;0;1))

just change the range searched from A1:I14 to suit your needs.
Array formulas have to be entered using the Ctrl+Shift+Enter key combination and in Calc you cannot use the fill handle to copy it to neighbouring cells, the first cell with the formula has to be copied then it can be pasted into other cells using the Paste button or Ctrl+V

Ken Johnson

[acknak]

Here's one approach:
=SUM(
SUMPRODUCT(A1:A100="productA";B1:B100);
SUMPRODUCT(C1:C100="productA";D1:D100);
SUMPRODUCT(E1:E100="productA";F1:F100))

You may have to enter it all on one line to get Calc to accept it.

Since the information is spread over several columns, I can't think of a way to do the sum all at once.

[s.tsiolas]

I have tried both options but it seams that they cannot work. Can you please upload a small sample just to see it in action?

Thanks a lot!

[ken johnson]

Attached doc shows both approaches in action.
Ken Johnson

Sum Product Weights.ods

(17.42 KiB) Downloaded 5 times