Hi. I need help with creating a formula that will sum the lesser value between two columns when a condition in another column is met.
Example: Sum the lesser value between columns B and C when column A contains "x".
...A...B...C
1.....3.....2
2.x...4....1
3
4.x...2....3
A2 and A4 contain "x". The lesser value between B2 and C2 is 1. The lesser value between B4 and C4 is 2. So 1 + 2 = 3. The sum should be 3.
MacBook, Mac OS X (10.6.8)
add a new column (let's say columns D) that finds the min of the two columns
=min(B, C)
then use sumif(A, "X", D)
like this:
D1=MIN(B1:C1)
this is shorthand for... select cell D1, then type (or copy and paste from here) the formula:
=MIN(B1:C1)
select cell D1, copy
select column D (by clicking the "D" at the very top of the column), paste
E1=SUMIF(A,"X",D )
Thanks for the help, but is there a way to do this without having to create another column?
This is the formula that I used in Excel:
=SUMPRODUCT((B<=C)*(B)*(A="x")+(B>C)*(C)*(A="x"))
Is there a way to "convert" this formula to a Numbers formula?
That is an "array" formula. They don't work in Numbers. What's wrong with adding another column? You can hide it after you have it all set up.
I cannot think of a way that does not use another column.
Here's another way...:
D1=IF(A1="X", MIN(B1:C1), "")
fill D1 down as needed
sum column D where ever you want
I think the two ways I posted are easier to read that the array-based formal you originally posted. If you really need/want array functions then I do not think Numbers is the right tool for you.
there may be other ways if there are someone may yet see it.
All the best,
Wayne
Ok, thanks for your help. I will just stick to using Excel, unfortunately, until I find the time to learn how to write code so I can write my own program to solve problems.
you can learn python playing a game called code combat.
Sum the lesser of two columns when condition is met
