Help simplifying formula

ssiegler

New Member
Joined
Oct 19, 2009
Messages
5
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............
 
Upvote 0
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
 
Upvote 0
ADVERTISEMENT
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))
 
Upvote 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
 
Upvote 0
469346
010101
000000
010000
010100
0669915
@@@@
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>
 
Upvote 0

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top