Solved

# Need help to shorten array formula in MS Excel

Posted on 2007-10-04
Medium Priority
284 Views
Last Modified: 2010-05-18
Is there a shorter way of stating the following array formula in MS Excel:
=SUM((\$I\$2:\$I\$25017=0)*(\$BN\$2:\$BN\$25017))+SUM((\$I\$2:\$I\$25017=86)*(\$BN\$2:\$BN\$25017))+SUM((\$I\$2:\$I\$25017=89)*(\$BN\$2:\$BN\$25017))+SUM((\$I\$2:\$I\$25017=92)*(\$BN\$2:\$BN\$25017))+SUM((\$I\$2:\$I\$25017=93)*(\$BN\$2:\$BN\$25017))

Would the use of OR be possible?
0
Question by:VHT
• 2
4 Comments

LVL 33

Expert Comment

ID: 20013330
Did you tried something like:
=sumproduct((\$I\$2:\$I\$25017=0)*(\$BN\$2:\$BN\$25017)*OR((\$I\$2:\$I\$25017=86),SUM((\$I\$2:\$I\$25017=89),(\$I\$2:\$I\$25017=92),(\$I\$2:\$I\$25017=93)))

Keep in mind that you don't need the ARRAY keys for this

regards,
Jeroen
0

LVL 85

Assisted Solution

Rory Archibald earned 200 total points
ID: 20013579
You can use:
=SUMPRODUCT(\$BN\$2:\$BN\$25017*(\$I\$2:\$I\$25017={0,86,89,92,93}))
also entered normally.
Regards,
Rory
0

LVL 33

Accepted Solution

Jeroen Rosink earned 400 total points
ID: 20013634
Even better, forgot about entering the array in SUMPRODUCT.
Indeed my function should be :
=SUMPRODUCT((\$BN\$2:\$BN\$25017)*OR((\$I\$2:\$I\$25017=0),(\$I\$2:\$I\$25017=86),SUM((\$I\$2:\$I\$25017=89),(\$I\$2:\$I\$25017=92),(\$I\$2:\$I\$25017=93))))

made a typo due to all the conditions
0

LVL 6

Assisted Solution

mwolfe02 earned 400 total points
ID: 20013692
=SUM(IF((\$I\$2:\$I\$25017=0)+(\$I\$2:\$I\$25017=86)+(\$I\$2:\$I\$25017=89)+(\$I\$2:\$I\$25017=92)+(\$I\$2:\$I\$25017=93),\$BN\$2:\$BN\$25017,0))

Another way to shorten this formula further and enhance readability is by naming your ranges (see Excel help for details on this).

If you name \$I\$2:\$I\$25017 = "One" and name \$BN\$2:\$BN\$25017 = "Two" your formula would be (I would suggest you actually use meaningful names when you implement this in your workbook):

=SUM(IF((One=0)+(One=86)+(One=89)+(One=92)+(One=93),Two,0))

Hope that helps,
mike
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calculâ€¦
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
###### Suggested Courses
Course of the Month14 days, left to enroll

#### 807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.