Multiplying cells conditionally

[kampi]

Hi all,

New to calc.

I'd like to multiply the values in row based on the content of another row. How can I do that?
Example:
A B C D E
1 0 1 0 1 1
2 1 2 3 4 5
In this case, I'd like to multiply all values in Row2 at the places where Row1 is greater than one. The result should be: 2*4*5=40.

Any ideas?

[RusselB]

As long as your using 1's and 0's for your determining factor, then the SUMPRODUCT function is ideal.

Code: Select all

=sumproduct(A1:E1;A2:E2)

[lader]

Enter as a matrix command [Ctrl+Shift+Enter]

Code: Select all

{=PRODUCT(IF(A1:F1>=1; A2:F2; 1))}

[Lupp]

(Editing: @kampi: I just found this was your first post. Welcome to the forum!)

@RusselB: Your formula will return 0 as soon as (at least) one of the numbers in the row of conditions is 0.

@kampi: Your conditional "...multiply all values in Row2 at the places where Row1 is greater than one" makes the result being 1 without calculation since the condition is not met at any place. You surely meant "greater than one or equal to one" (>=1). The other contributors having answered above assumed this seemingly.

A hint insofar: It's often not the best idea to do things that are often needed in a different way as it's usually done.
If your first row actually is meant to give a logical value, you should know that Calc by default accepts and interprets any number different fom 0 as a representation of TRUE.
Of course you are free to derive logical results from numeric values in a different way, but this should be restricted to cases where actually needed.
Regarding that hint "lader"'s formula might be changed to

Code: Select all

{=PRODUCT(IF(A1:F1<>0; A2:F2; 1))}
or just
{=PRODUCT(IF(A1:F1; A2:F2; 1))}

(I wouldn't actually recommend the shorthand version.)
Since formulas entered for array-evaluation show a somehow stubborn behaviour concerning fill operations by dragging the fill handle with the mouse, I try to avoid it in cases where this is rather simple (a workaround based on the SUMPRODUCT() function again). If you want to get additional info you may look into the attached example.