Help simplifying formula

[ssiegler]

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.

 

[oldbrewer]

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............

 

[ssiegler]

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

 

[VBA Geek]

control shift enter

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

 

[VBA Geek]

Row 1 is a list of values

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

or this

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

 

[oldbrewer]

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

 

[oldbrewer]

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>