# Help simplifying formula

#### ssiegler

##### New Member
Hello,
I need help simplifying the following formula. It screams for sumproduct or an array formula but i just cant figure it out. Any help would be greatly appreciated.

= A1*INT(OR(A2:A5)) + B1*INT(OR(B2:B5)) + C1*INT(OR(C2:C5)) + ...

The issue comes down to using OR in an array formula or creating the same functionality. Again, any help would really be appreciated.

Thanks.

### Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
tell us in plain and simple speak what you are trying to do

is it take any value from A2:A5 and add to any value from B2;B5 and if say total is more than 100 return total otherwise return blank............

Row 1 is a list of values

Rows 2, 3, 4 and 4 have values 0 or 1.

I need the sum of values in row 1 in which any value in the column (rows 2-5) has a 1. So, functionality wise, I need to OR the column and multiply that with the value in row 1

control shift enter

Code:
``=INDEX(MMULT(A1:F1,IF(MMULT(--TRANSPOSE(A2:F5),ROW(A2:A5)^0),1,0)),1)``

Row 1 is a list of values

Rows 2, 3, 4 and 4 have values 0 or 1.

or this
Code:
``=SUMPRODUCT(A1:F1,IFERROR(SUBTOTAL(9,OFFSET(A2:A5,0,{0,1,2,3,4,5}))^0,0))``

if you sum rows 2:5 and test if total is > 0 and if it is make total = 1 you then simply add A1 x total plus B1 x total etc

 4 6 9 3 4 6 0 1 0 1 0 1 0 0 0 0 0 0 0 1 0 0 0 0 0 1 0 1 0 0 0 6 6 9 9 15 @@@@ formula in @@@@ =IF(SUM(B2:B5)>0,B1+A6,0+A6) so the bottom row is a running total of numbers in row 1 where there is at least one 1 in rows 2 to 5

<colgroup><col width="64" span="12" style="width:48pt"> </colgroup><tbody>
</tbody>

Replies
2
Views
108
Replies
7
Views
94
Replies
4
Views
63
Replies
18
Views
197
Replies
4
Views
120

1,196,516
Messages
6,015,678
Members
441,915
Latest member
sm Hussaini

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back