[SOLVED] If/then for different columns

[raynold3228]

Hi all, my first question on this forum.

The situation: lets say i have 2 columns, A and B. All values in A are alphabetical (either 'Buy' or 'Sell); all values in B are numerical (e.g. 1000, -1400).

Here is what i want to do: i want to sum up only the values in column B for which their corresponding value in column A is "Buy". In other words, if column A='Buy' for rows 1, 3, and 4, I want to sum up the values in column B for rows 1, 3, and 4 only.

How can i achieve this? I've tried playing with if and sumif functions to no avail. all help appreciated. thanks!

[robleyd]

You didn't mention exactly what you have tried.

In C1, try

Code: Select all

=SUMIF(A1:A5;"Buy";B1:B5)

From the Help - F1:

Syntax

SUMIF(Range; Criteria; SumRange)

Range is the range to which the criteria are to be applied.
Criteria is a reference to a cell containing the search criterion, or the search criterion itself. If the criterion is written into the formula, it must be enclosed with double quotes.
SumRange is the range from which values are summed. If this parameter has not been indicated, the values found in the Range are summed.

[Lupp]

(Intended as clarification.)

The example values from the OriginalQuestion are 1000 (positive) and -1400 (negative). This makes me think that "Sell" should be associated with a positive value (expectable cash-in) and "Buy" with a negative value (expectable cash-out).
If so you may either have expressed the cash-in/cash-out alternative in two redundant ways which would violate a basic principle of keeping data, or you are using formulae already deriving the values from given prices and quantities assigning the appropriate sign based on the "Sell"/"Buy" alternative, in which case formulae applying that decision a second time would introduce another kind of redundancy.

If the sign of the total price per row actually contains the information about "Sell"/"Buy", you shouldn't use SUMIF() based on "Sell"/"Buy", but something like

Code: Select all

=SUMPRODUCT($B$2:$B$1001;$B$2:$B$1001<0)

where the secound parameter returns 1 for rows with a negative value, and 0 for rows with a positive (or zero) value.

Though I cannot easily imagine a "Sell" with a negative value (negative cash-in ?), the negative signs may be created for different reasons ("Gain"/"Loss" e.g. but: how applicable with "Buy"), I would prefer something like

Code: Select all

=SUMPRODUCT($B$2:$B$1001;$A$2:$B$1001="Buy")

again because the order of the parameters of SUMIF() is not very logical for historical bad reasons. (Should be SUM something IF the following condition holds, but is IF the condition to the right holds SUM the range to the left.)

By the way: More recent versions of Calc in Libreoffice have implemented the function SUMIFS() which
-1- reverses the order to the more logical one
-2- offers the choice to append as many pairs Range/Criterion as wanted.

[raynold3228]

Thanks everyone!