More Efficient Formula to Shorten Long IF Statement

Basically, I have a costing IF statement basically it looks to 6 cells, M4:M9, to see what "cost types" were selected by the user then sums them if they were.

I KNOW that I can make dynamic ranges for many ranges in this formula (especially where I'm using an isnumber(match against and entire Column (ConstrainedReqs!$B:$B); but that's not really going to shorten it very significantly. Further, right now I only have 6 cost types in the formula, truth is that there might be 30 costs types before I'm done; exponentially increasing the length. Can it be written in a better way?

(Before you ask, I've thought about "packaging" costs instead of giving flexibility over each cost type/component, I like the flexibility that selecting each type/component offers)  

=IF($M$4="Accident / Hazardous Incident",SUMPRODUCT((AircraftData!$E$2:$E$301=AnalysisEngine!$B18)*(ISNUMBER(MATCH(AircraftData!$D$2:$D$301,ConstrainedReqs!$B:$B,0))*(AircraftData!$K$2:$K$301))),IF($M$4="Routine Maintenance / Inspections",SUMPRODUCT((AircraftData!$E$2:$E$301=AnalysisEngine!$B18)*(ISNUMBER(MATCH(AircraftData!$D$2:$D$301,ConstrainedReqs!$B:$B,0))*(AircraftData!$L$2:$L$301))),IF($M$4="Modifications",SUMPRODUCT((AircraftData!$E$2:$E$301=AnalysisEngine!$B18)*(ISNUMBER(MATCH(AircraftData!$D$2:$D$301,ConstrainedReqs!$B:$B,0))*(AircraftData!$M$2:$M$301))),IF($M$4="Paint and Refurbishment",SUMPRODUCT((AircraftData!$E$2:$E$301=AnalysisEngine!$B18)*(ISNUMBER(MATCH(AircraftData!$D$2:$D$301,ConstrainedReqs!$B:$B,0))*(AircraftData!$N$2:$N$301))),IF($M$4="Corrosion",SUMPRODUCT((AircraftData!$E$2:$E$301=AnalysisEngine!$B18)*(ISNUMBER(MATCH(AircraftData!$D$2:$D$301,ConstrainedReqs!$B:$B,0))*(AircraftData!$O$2:$O$301))),IF($M$4="Acquisiton",SUMPRODUCT((AircraftData!$E$2:$E$301=AnalysisEngine!$B18)*(ISNUMBER(MATCH(AircraftData!$D$2:$D$301,ConstrainedReqs!$B:$B,0))*(AircraftData!$P$2:$P$301))),0))))))+IF($M$5="Accident / Hazardous Incident",SUMPRODUCT((AircraftData!$E$2:$E$301=AnalysisEngine!$B18)*(ISNUMBER(MATCH(AircraftData!$D$2:$D$301,ConstrainedReqs!$B:$B,0))*(AircraftData!$K$2:$K$301))),IF($M$5="Routine Maintenance / Inspections",SUMPRODUCT((AircraftData!$E$2:$E$301=AnalysisEngine!$B18)*(ISNUMBER(MATCH(AircraftData!$D$2:$D$301,ConstrainedReqs!$B:$B,0))*(AircraftData!$L$2:$L$301))),IF($M$5="Modifications",SUMPRODUCT((AircraftData!$E$2:$E$301=AnalysisEngine!$B18)*(ISNUMBER(MATCH(AircraftData!$D$2:$D$301,ConstrainedReqs!$B:$B,0))*(AircraftData!$M$2:$M$301))),IF($M$5="Paint and Refurbishment",SUMPRODUCT((AircraftData!$E$2:$E$301=AnalysisEngine!$B18)*(ISNUMBER(MATCH(AircraftData!$D$2:$D$301,ConstrainedReqs!$B:$B,0))*(AircraftData!$N$2:$N$301))),IF($M$5="Corrosion",SUMPRODUCT((AircraftData!$E$2:$E$301=AnalysisEngine!$B18)*(ISNUMBER(MATCH(AircraftData!$D$2:$D$301,ConstrainedReqs!$B:$B,0))*(AircraftData!$O$2:$O$301))),IF($M$5="Acquisiton",SUMPRODUCT((AircraftData!$E$2:$E$301=AnalysisEngine!$B18)*(ISNUMBER(MATCH(AircraftData!$D$2:$D$301,ConstrainedReqs!$B:$B,0))*(AircraftData!$P$2:$P$301))),0))))))+ IF($M$6="Accident / Hazardous Incident",SUMPRODUCT((AircraftData!$E$2:$E$301=AnalysisEngine!$B18)*(ISNUMBER(MATCH(AircraftData!$D$2:$D$301,ConstrainedReqs!$B:$B,0))*(AircraftData!$K$2:$K$301))),IF($M$6="Routine Maintenance / Inspections",SUMPRODUCT((AircraftData!$E$2:$E$301=AnalysisEngine!$B18)*(ISNUMBER(MATCH(AircraftData!$D$2:$D$301,ConstrainedReqs!$B:$B,0))*(AircraftData!$L$2:$L$301))), IF($M$6="Modifications",SUMPRODUCT((AircraftData!$E$2:$E$301=AnalysisEngine!$B18)*(ISNUMBER(MATCH(AircraftData!$D$2:$D$301,ConstrainedReqs!$B:$B,0))*(AircraftData!$M$2:$M$301))), IF($M$6="Paint and Refurbishment",SUMPRODUCT((AircraftData!$E$2:$E$301=AnalysisEngine!$B18)*(ISNUMBER(MATCH(AircraftData!$D$2:$D$301,ConstrainedReqs!$B:$B,0))*(AircraftData!$N$2:$N$301))), IF($M$6="Corrosion",SUMPRODUCT((AircraftData!$E$2:$E$301=AnalysisEngine!$B18)*(ISNUMBER(MATCH(AircraftData!$D$2:$D$301,ConstrainedReqs!$B:$B,0))*(AircraftData!$O$2:$O$301))), IF($M$6="Acquisiton",SUMPRODUCT((AircraftData!$E$2:$E$301=AnalysisEngine!$B18)*(ISNUMBER(MATCH(AircraftData!$D$2:$D$301,ConstrainedReqs!$B:$B,0))*(AircraftData!$P$2:$P$301))),0))))))+ IF($M$7="Accident / Hazardous Incident",SUMPRODUCT((AircraftData!$E$2:$E$301=AnalysisEngine!$B18)*(ISNUMBER(MATCH(AircraftData!$D$2:$D$301,ConstrainedReqs!$B:$B,0))*(AircraftData!$K$2:$K$301))),IF($M$7="Routine Maintenance / Inspections",SUMPRODUCT((AircraftData!$E$2:$E$301=AnalysisEngine!$B18)*(ISNUMBER(MATCH(AircraftData!$D$2:$D$301,ConstrainedReqs!$B:$B,0))*(AircraftData!$L$2:$L$301))), IF($M$7="Modifications",SUMPRODUCT((AircraftData!$E$2:$E$301=AnalysisEngine!$B18)*(ISNUMBER(MATCH(AircraftData!$D$2:$D$301,ConstrainedReqs!$B:$B,0))*(AircraftData!$M$2:$M$301))), IF($M$7="Paint and Refurbishment",SUMPRODUCT((AircraftData!$E$2:$E$301=AnalysisEngine!$B18)*(ISNUMBER(MATCH(AircraftData!$D$2:$D$301,ConstrainedReqs!$B:$B,0))*(AircraftData!$N$2:$N$301))), IF($M$7="Corrosion",SUMPRODUCT((AircraftData!$E$2:$E$301=AnalysisEngine!$B18)*(ISNUMBER(MATCH(AircraftData!$D$2:$D$301,ConstrainedReqs!$B:$B,0))*(AircraftData!$O$2:$O$301))), IF($M$7="Acquisiton",SUMPRODUCT((AircraftData!$E$2:$E$301=AnalysisEngine!$B18)*(ISNUMBER(MATCH(AircraftData!$D$2:$D$301,ConstrainedReqs!$B:$B,0))*(AircraftData!$P$2:$P$301))),0)))))) + IF($M$8="Accident / Hazardous Incident",SUMPRODUCT((AircraftData!$E$2:$E$301=AnalysisEngine!$B18)*(ISNUMBER(MATCH(AircraftData!$D$2:$D$301,ConstrainedReqs!$B:$B,0))*(AircraftData!$K$2:$K$301))),IF($M$8="Routine Maintenance / Inspections",SUMPRODUCT((AircraftData!$E$2:$E$301=AnalysisEngine!$B18)*(ISNUMBER(MATCH(AircraftData!$D$2:$D$301,ConstrainedReqs!$B:$B,0))*(AircraftData!$L$2:$L$301))), IF($M$8="Modifications",SUMPRODUCT((AircraftData!$E$2:$E$301=AnalysisEngine!$B18)*(ISNUMBER(MATCH(AircraftData!$D$2:$D$301,ConstrainedReqs!$B:$B,0))*(AircraftData!$M$2:$M$301))), IF($M$8="Paint and Refurbishment",SUMPRODUCT((AircraftData!$E$2:$E$301=AnalysisEngine!$B18)*(ISNUMBER(MATCH(AircraftData!$D$2:$D$301,ConstrainedReqs!$B:$B,0))*(AircraftData!$N$2:$N$301))), IF($M$8="Corrosion",SUMPRODUCT((AircraftData!$E$2:$E$301=AnalysisEngine!$B18)*(ISNUMBER(MATCH(AircraftData!$D$2:$D$301,ConstrainedReqs!$B:$B,0))*(AircraftData!$O$2:$O$301))), IF($M$8="Acquisiton",SUMPRODUCT((AircraftData!$E$2:$E$301=AnalysisEngine!$B18)*(ISNUMBER(MATCH(AircraftData!$D$2:$D$301,ConstrainedReqs!$B:$B,0))*(AircraftData!$P$2:$P$301))),0))))))+ IF($M$9="Accident / Hazardous Incident",SUMPRODUCT((AircraftData!$E$2:$E$301=AnalysisEngine!$B18)*(ISNUMBER(MATCH(AircraftData!$D$2:$D$301,ConstrainedReqs!$B:$B,0))*(AircraftData!$K$2:$K$301))),IF($M$9="Routine Maintenance / Inspections",SUMPRODUCT((AircraftData!$E$2:$E$301=AnalysisEngine!$B18)*(ISNUMBER(MATCH(AircraftData!$D$2:$D$301,ConstrainedReqs!$B:$B,0))*(AircraftData!$L$2:$L$301))), IF($M$9="Modifications",SUMPRODUCT((AircraftData!$E$2:$E$301=AnalysisEngine!$B18)*(ISNUMBER(MATCH(AircraftData!$D$2:$D$301,ConstrainedReqs!$B:$B,0))*(AircraftData!$M$2:$M$301))), IF($M$9="Paint and Refurbishment",SUMPRODUCT((AircraftData!$E$2:$E$301=AnalysisEngine!$B18)*(ISNUMBER(MATCH(AircraftData!$D$2:$D$301,ConstrainedReqs!$B:$B,0))*(AircraftData!$N$2:$N$301))), IF($M$9="Corrosion",SUMPRODUCT((AircraftData!$E$2:$E$301=AnalysisEngine!$B18)*(ISNUMBER(MATCH(AircraftData!$D$2:$D$301,ConstrainedReqs!$B:$B,0))*(AircraftData!$O$2:$O$301))), IF($M$9="Acquisiton",SUMPRODUCT((AircraftData!$E$2:$E$301=AnalysisEngine!$B18)*(ISNUMBER(MATCH(AircraftData!$D$2:$D$301,ConstrainedReqs!$B:$B,0))*(AircraftData!$P$2:$P$301))),0))))))
LVL 1
-PolakAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rory ArchibaldCommented:
Hard to answer without seeing the workbook, but do you have headers in AircraftData!K1:P1 that match the values that can be selected in M4:M9?
0
-PolakAuthor Commented:
Sorry about that, limitation of this data, have to sanitize it before I share and that typically takes a bit of work to recreate a "dummy" version. If it is very necessary let me know and I'll do it.

No I don't, but that can certainly be changed at this point, I'm guessing you're about to suggest an Index/Match/VLookup function. I tried that, but kept running into errors (probably syntax) so I gave up and did the above monster instead. I'm open to that if that's the best solution...
0
Rob HensonFinance AnalystCommented:
That is indeed a monster of a formula!!!

Would you be able to split the various elements into a matrix arrangement:

Column1                                                   Column2
Accident / Hazardous Incident              Cost for that element
Routine Maintenance / Inspections      Cost for that element
Modifications                                            Cost for that element
Paint and Refurbishment                       Cost for that element
Corrosion                                                  Cost for that element
Acquisiton                                                 Cost for that element

The cost for each element would be the various SUMPRODUCT formulas.

You would then simplify summary formula to:

=SUMIF(Column1,M4,Column2)+SUMIF(Column1,M5,Column2)+SUMIF(Column1,M6,Column2)+SUMIF(Column1,M7,Column2)+SUMIF(Column1,M8,Column2)+SUMIF(Column1,M9,Column2)

Thanks
Rob H
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

-PolakAuthor Commented:
Rob Henson; I thought about that too, but the source data would have to get more complex and be queried differently because.... (I'll explain it in terms of the formula):

"SUMPRODUCT((AircraftData!$E$2:$E$301=AnalysisEngine!$B18)": this part of the formula matches the aircraft type as the type that you are costing for.

*(ISNUMBER(MATCH(AircraftData!$D$2:$D$301,ConstrainedReqs!$B:$B,0)): this part of the formula match the aircraft types you are costing for ONLY in the operating UNITS that have been user-selected in ConstrainedReqs!$B:$B

So what does that mean.... there are 5 regions > 30 branches > 40 Units> 20 different types of aircraft > 30 different types of costs. I think you can visualize the problem I'm talking about (especially in a flat table like excel).... Moreover, other data columns would have a lot of trouble with things like double counting.

I do think that a combination of Index Match and VLOOKUP can help here; however, if it can't, and I do end up with 30 different types of costs, is there a limit to how many IF statements I can make in an argument?
0
Saurabh Singh TeotiaCommented:
I noticed that in each condition what is changing is you area of which you are doing sum..rest of the things remain the same and their is no change in that...

I have setup a dummy sheet to explain to you what you can do to make your life easy..Now basis of your input of rng the formula will give you the updated results..

You can set this up in your environment by defining a range table from where you can do the vlookup of and can do what you are looking for...

Saurabh...
dummy-data.xlsx
0
Ejgil HedegaardCommented:
If you put the 6 selectable values from M4:M9
Accident / Hazardous Incident
Routine Maintenance / Inspections
Modifications
Paint and Refurbishment
Corrosion
Acquisiton
in K1:P1 on AircraftData, as headers for the columns to sum, then this (long) formula will do the same as your very long formula.
=IFERROR(SUMPRODUCT((AircraftData!$E$2:$E$301=AnalysisEngine!$B$18)*(ISNUMBER(MATCH(AircraftData!$D$2:$D$301,ConstrainedReqs!$B:$B,0))*(INDEX(AircraftData!$K$2:$P$301,,MATCH(M4,AircraftData!$K$1:$P$1,0))))),0)+IFERROR(SUMPRODUCT((AircraftData!$E$2:$E$301=AnalysisEngine!$B$18)*(ISNUMBER(MATCH(AircraftData!$D$2:$D$301,ConstrainedReqs!$B:$B,0))*(INDEX(AircraftData!$K$2:$P$301,,MATCH(M5,AircraftData!$K$1:$P$1,0))))),0)+IFERROR(SUMPRODUCT((AircraftData!$E$2:$E$301=AnalysisEngine!$B$18)*(ISNUMBER(MATCH(AircraftData!$D$2:$D$301,ConstrainedReqs!$B:$B,0))*(INDEX(AircraftData!$K$2:$P$301,,MATCH(M6,AircraftData!$K$1:$P$1,0))))),0)+IFERROR(SUMPRODUCT((AircraftData!$E$2:$E$301=AnalysisEngine!$B$18)*(ISNUMBER(MATCH(AircraftData!$D$2:$D$301,ConstrainedReqs!$B:$B,0))*(INDEX(AircraftData!$K$2:$P$301,,MATCH(M7,AircraftData!$K$1:$P$1,0))))),0)+IFERROR(SUMPRODUCT((AircraftData!$E$2:$E$301=AnalysisEngine!$B$18)*(ISNUMBER(MATCH(AircraftData!$D$2:$D$301,ConstrainedReqs!$B:$B,0))*(INDEX(AircraftData!$K$2:$P$301,,MATCH(M8,AircraftData!$K$1:$P$1,0))))),0)+IFERROR(SUMPRODUCT((AircraftData!$E$2:$E$301=AnalysisEngine!$B$18)*(ISNUMBER(MATCH(AircraftData!$D$2:$D$301,ConstrainedReqs!$B:$B,0))*(INDEX(AircraftData!$K$2:$P$301,,MATCH(M9,AircraftData!$K$1:$P$1,0))))),0)

The formula has a part for each value in M4:M9.
The first (before the first +) is for M4
IFERROR(SUMPRODUCT((AircraftData!$E$2:$E$301=AnalysisEngine!$B$18)*(ISNUMBER(MATCH(AircraftData!$D$2:$D$301,ConstrainedReqs!$B:$B,0))*(INDEX(AircraftData!$K$2:$P$301,,MATCH(M4,AircraftData!$K$1:$P$1,0))))),0)
and the other parts are for M5 to M9.
All parts are identical, except the reference to M4 to M9.
The last part of the formula INDEX(AircraftData!$K$2:$P$301,,MATCH(M4,AircraftData!$K$1:$P$1,0)) reads the entire column K2:K301, L2:L301 etc. depending of the match for M4, and does in principle the same as all the if statements in the very long formula.
0
byundtMechanical EngineerCommented:
I put your 6 selectable values from M4:M9 into a named range xxxxxx. I believe you can then use an array-entered formula like:
=SUM(IFERROR(SUMPRODUCT((AircraftData!$E$2:$E$301=AnalysisEngine!$B18)*(ISNUMBER(MATCH(AircraftData!$D$2:$D$301,ConstrainedReqs!$B:$B,0))*(INDEX(AircraftData!$K$2:$P$301,,MATCH($M$4:$M$9,xxxxx,0))))),0))
0
byundtMechanical EngineerCommented:
In support of my previously suggested very short formula, notice how I added line feeds and spaces in the textbox in Sheet1 of the attached workbook. After adding these line feeds and spaces, the parallelism of your existing formula becomes obvious--thus suggesting the simplification in my previous post.

The formula is accepted by Excel, but has not been challenged with data.
MonsterFormulaQ28634001.xlsx
0
byundtMechanical EngineerCommented:
I tried plugging some numbers and found my array formula wasn't working. I believe Ejgil Hedegaard has the better approach.
0
aikimarkCommented:
This is a first approximation of a basic User-Defined function.  It will need to be tweaked to refer to WorksheetFunctions, worksheets, and ranges, although you might be able to Evaluate the function.  However, the Evaluate wouldn't be a very good performer.
Public Function GetCosting(ByVal parmCategory)
    Select Case parmCategory
        Case "Accident / Hazardous Incident"
            GetCosting = SUMPRODUCT((AircraftData!$E$2:$E$301=AnalysisEngine!$B18) * _
            (ISNUMBER(MATCH(AircraftData!$D$2:$D$301,ConstrainedReqs!$B:$B,0) * _
            (AircraftData!$K$2:$K$301)))
        Case "Routine Maintenance / Inspections"
            GetCosting = SUMPRODUCT((AircraftData!$E$2:$E$301=AnalysisEngine!$B18) * _
            (ISNUMBER(MATCH(AircraftData!$D$2:$D$301,ConstrainedReqs!$B:$B,0)) * _
            (AircraftData!$L$2:$L$301)))
        Case "Modifications"
            GetCosting = SUMPRODUCT((AircraftData!$E$2:$E$301=AnalysisEngine!$B18) * _
            (ISNUMBER(MATCH(AircraftData!$D$2:$D$301,ConstrainedReqs!$B:$B,0)) * _
            (AircraftData!$M$2:$M$301)))
        Case "Paint and Refurbishment"
            GetCosting = SUMPRODUCT((AircraftData!$E$2:$E$301=AnalysisEngine!$B18) * _
            (ISNUMBER(MATCH(AircraftData!$D$2:$D$301,ConstrainedReqs!$B:$B,0)) * _
            (AircraftData!$N$2:$N$301)))
        Case "Corrosion"
            GetCosting = SUMPRODUCT((AircraftData!$E$2:$E$301=AnalysisEngine!$B18) * _
            (ISNUMBER(MATCH(AircraftData!$D$2:$D$301,ConstrainedReqs!$B:$B,0)) * _
            (AircraftData!$O$2:$O$301)))
        Case "Acquisiton"
            GetCosting = SUMPRODUCT((AircraftData!$E$2:$E$301=AnalysisEngine!$B18) * _
            (ISNUMBER(MATCH(AircraftData!$D$2:$D$301,ConstrainedReqs!$B:$B,0)) * _
            (AircraftData!$P$2:$P$301)))
        Case Else
            GetCosting = 0
    End Select
End Function

Open in new window

Once the function is in place, you can replace your original formula with this:
=GetCosting($M$4)+GetCosting($M$5)+GetCosting($M$6)+GetCosting($M$7)+GetCosting($M$8)+GetCosting($M$9)

Open in new window

0
Rory ArchibaldCommented:
Absent a sample workbook, I can't do rigorous testing but I think this does the same as your original formula:

=SUM(MMULT(ISNUMBER(MATCH(AircraftData!$K$1:$P$1,$M$4:$M$9,0))+0,TRANSPOSE((AircraftData!$E$2:$E$301=AnalysisEngine!$B18)*(ISNUMBER(MATCH(AircraftData!$D$2:$D$301,ConstrainedReqs!$B:$B,0)))*AircraftData!$K$2:$P$301)))

array-entered with Ctrl+Shift+Enter.
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
-PolakAuthor Commented:
I really appreciate everyone's input on this problem, I hope you are finding it as interesting as me...

Saurabh & Rob Henson; yes, I agree that the data needs to be formatted the way you are suggesting it would allow for better analysis down the road. I will redo the query when I'm closer to finalizing, currently I'm just building the framework for the analysis tool.

Rory Archibald I just tested your array using the MMULT function. I didn't know that even existed. It works perfectly.... And its really cool...

I didn't move forward with testing aikimark and Ejgil Hedegaard's solutions as they seem less efficient then the MMULT array. Any objections before I call that the best solution?
0
aikimarkCommented:
You might want to test the performance of the two solutions.  However, since you were looking for a formula simplification, Rory's pure formula solution would be a natural selection.  My solution is a simpler/shorter formula, but requires a UDF.
0
-PolakAuthor Commented:
At the moment, with the size of the source data, there isn't a noticeable degradation in performance between either solution. This might not hold true if I was testing with the full 300,000 line data set. However, if I were using the full data set, I should probably query it more in-line with how Saurabh & Rob Henson suggested.

As such, I'll award the majority of Points to Rory with assists to the other experts for the alternative approaches when dealing with the final data set.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.