Multiplying cells conditionally

Discuss the spreadsheet application

Multiplying cells conditionally

Postby kampi » Sun Jan 26, 2020 1:38 am

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?
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
Posts: 1
Joined: Sun Jan 26, 2020 1:32 am

Re: Multiplying cells conditionally

Postby RusselB » Sun Jan 26, 2020 4:13 am

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
OpenOffice 4.1.7 and LibreOffice on Windows 7 Pro & Ultimate
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.
User avatar
Posts: 6097
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Multiplying cells conditionally

Postby lader » Sun Jan 26, 2020 5:27 am

Enter as a matrix command [Ctrl+Shift+Enter]
Code: Select all   Expand viewCollapse view
{=PRODUCT(IF(A1:F1>=1; A2:F2; 1))}
LibreOffice on Ubuntu 20.04.1 LTS
Posts: 24
Joined: Mon Jul 02, 2018 6:10 pm

Re: Multiplying cells conditionally

Postby Lupp » Sun Jan 26, 2020 1:18 pm

(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.
(20.5 KiB) Downloaded 12 times
On Windows 10: LibreOffice 7.0 and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
Lupp from München
User avatar
Posts: 2910
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Return to Calc

Who is online

Users browsing this forum: No registered users and 24 guests