# Multiply cells in a range based on criteria

#### polarisking

##### Member
I should be able to do this, but I'm having difficulty. I have a range of values (A1:A5). Based on whether the value is Positive or Negative, I want to MULTIPLY the values together.

Example
1
2
-3
4
-5

The formula (certainly an array/CSE type, I would think) would arrive at 8 (1* 2 * 4) for the positive set, and 15 (-3 * -5) for the negative set.

Actually, this is a return compounding exercise, so each value gets divided by 100 and then added to 1.

So the range becomes
1.01
1.02
.97
1.04
.95

I'd like to do this not using helper columns. I tried something like

SUMPRODUCT((1+A1:A5/100)*--(A1:A5>=0)) for the positive value and
SUMPRODUCT((1+A1:A5/100)*--(A1:A5<0)) for the negative value.

The result is the sum of the eligible values. I'm looking for the PRODUCT of the eligible values.

The positive result should be 1.071408 and negative result should be 0.9215.

#### Attachments

• 10.2 KB Views: 4

#### Chihiro

##### Excel Ninja
For Positive:
=PRODUCT(IF(A1:A5>=0,(1+A1:A5/100),1))

For Negative:
=PRODUCT(IF(A1:A5<0,(1+A1:A5/100),1))

Confirmed as array (CTRL + SHIFT + ENTER)

• polarisking and herofox

#### herofox

##### Active Member
hello mr.
Chihiro
i have elso another without array , for NEGATIVE Result
Code:
``=SUMPRODUCT(--(\$A\$1:\$A\$5<0),\$A\$1:\$A\$5)/100+1``
for POSITIVE Result
Code:
``=SUMPRODUCT(--(\$A\$1:\$A\$5>-1),\$A\$1:\$A\$5)/100+1``

#### Attachments

• 10.7 KB Views: 5
• Hany ali and polarisking

#### bosco_yip

##### Excel Ninja
Or.............

For negative value sum:
=SUMPRODUCT(0+TEXT(A1:A5,"\0;-0"))/100+1

For positive value sum:
=SUMPRODUCT(0+TEXT(A1:A5,"0;\0"))/100+1

Regards
Bosco

• herofox

#### NARAYANK991

##### Excel Ninja
Hi ,

The answers given by Chihiro and the answers given by the others are doing two different calculations , resulting in different answers.

Which calculation is the right one depends on what the OP is looking for.

But going by the second example of the values

1.01
1.02
.97
1.04
.95

I think Chihiro's formula works correctly to return these values , whereas the other two do not.

Narayan

• bosco_yip

#### bosco_yip

##### Excel Ninja
Hi ,

The answers given by Chihiro and the answers given by the others are doing two different calculations , resulting in different answers.

Which calculation is the right one depends on what the OP is looking for.

But going by the second example of the values

1.01
1.02
.97
1.04
.95

I think Chihiro's formula works correctly to return these values , whereas the other two do not.

Narayan
Hi, Narayan

SUMPRODUCT() is plus range of data e.g. =A1+A2+A3, while PRODUCT() is multiply range of data e,g, =A1*A2*A3

So, please ignore my formulas post in level #.04 and the non-array formulas will be fixed by this :

1] Product of negative value result:
=PRODUCT(INDEX(TEXT(A1:A5,"\0;-0.00")/100+1,0))

2] Product of positive value result:
=PRODUCT(INDEX(TEXT(A1:A5,"0.00;\0")/100+1,0))

Regards
Bosco

Last edited:
• polarisking and NARAYANK991