## Multiplying cells conditionally

### Multiplying cells conditionally

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?
OpenOffice 4.1.7 on Windows 10
kampi

Posts: 1
Joined: Sun Jan 26, 2020 1:32 am

### Re: Multiplying cells conditionally

As long as your using 1's and 0's for your determining factor, then the SUMPRODUCT function is ideal.
Code: Select all   Expand viewCollapse view
=sumproduct(A1:E1;A2:E2)
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.

RusselB
Moderator

Posts: 6273
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

### Re: Multiplying cells conditionally

Enter as a matrix command [Ctrl+Shift+Enter]
Code: Select all   Expand viewCollapse view
{=PRODUCT(IF(A1:F1>=1; A2:F2; 1))}
LibreOffice 6.4.7.2 on Ubuntu 20.04.4 LTS

Posts: 31
Joined: Mon Jul 02, 2018 6:10 pm

### Re: Multiplying cells conditionally

(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   Expand viewCollapse view
{=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.
Attachments
aoo100884conditionallyAccumulatingProduct_1.ods
On Windows 10: LibreOffice 7.0 and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München

Lupp
Volunteer

Posts: 2980
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany