Can you help me shorten this formula?

[mrblister]

=A3+(SUMPRODUCT(
((Sheet1!$B$2:$B$35=J4)*(Sheet1!$N$2:$N$35=E$4)*(Sheet1!$S$2:$S$35)*J5)+
((Sheet1!$B$2:$B$35=K4)*(Sheet1!$N$2:$N$35=E$4)*(Sheet1!$S$2:$S$35)*K5)+
((Sheet1!$B$2:$B$35=L4)*(Sheet1!$N$2:$N$35=E$4)*(Sheet1!$S$2:$S$35)*L5)+
((Sheet1!$B$2:$B$35=M4)*(Sheet1!$N$2:$N$35=E$4)*(Sheet1!$S$2:$S$35)*M5)+
((Sheet1!$B$2:$B$35=N4)*(Sheet1!$N$2:$N$35=E$4)*(Sheet1!$S$2:$S$35)*N5)+
((Sheet1!$B$2:$B$35=O4)*(Sheet1!$N$2:$N$35=E$4)*(Sheet1!$S$2:$S$35)*O5)))

This is entered into A4, and copied down. It's actually a lot longer than this. I can turn 3 of the components above into named ranges. It looks like I can turn J4-O4 and J5-O5 into ranges as well, but when I try to I get errors.

Any options on how to shorten this formula?

 

[Eric W]

Always fun, seeing several bright people bouncing ideas off each other . . .

 

[mrblister]

Nice, considering post #2 totally misses the * operator in front of J5:O5!...

Lol! I was wondering why that wouldn't work. I figured I had incompatible data in one of my columns or something. I was going to ask though, what does the double negative in front of "--ISNUMBER" do?

 

[Aladin Akyurek]

Lol! I was wondering why that wouldn't work. I figured I had incompatible data in one of my columns or something. I was going to ask though, what does the double negative in front of "--ISNUMBER" do?

MATCH delivers a bunch of integers indicating matching positions if successful, otherwise #N/A.
Testing the MATCH delivery for consisting of numbers (integers) turns the successes into TRUE's, #N/A's to FALSE's.
the double minus (i.e. --) coerces/converts TRUE's to 1's, FALSE's to 0's. (In Excel TRUE = 1, FALSE = 0.)
This machinery is needed because the surrounding SUMPRODUCT needs numbers in order to calculate.

By the way, ISNUMBER\MATCH operates generally faster than an evaluation thru SUMIF(S).

 

[Rick Rothstein]

By the way, ISNUMBER\MATCH operates generally faster than an evaluation thru SUMIF(S).

Am I correct that the (corrected) formula I posted in Message #6 works correctly (hmm, what is with all those "corrects" ) and, if so, how does it rate speed-wise?

 

[mrblister]

MATCH delivers a bunch of integers indicating matching positions if successful, otherwise #N/A.
Testing the MATCH delivery for consisting of numbers (integers) turns the successes into TRUE's, #N/A's to FALSE's.
the double minus (i.e. --) coerces/converts TRUE's to 1's, FALSE's to 0's. (In Excel TRUE = 1, FALSE = 0.)
This machinery is needed because the surrounding SUMPRODUCT needs numbers in order to calculate.

By the way, ISNUMBER\MATCH operates generally faster than an evaluation thru SUMIF(S).

Thank you for this explanation! I'm going to write this down into my notes. Wait... sumproduct can't use true/false to calculate? Is that is all cases?

Am I correct that the (corrected) formula I posted in Message #6 works correctly (hmm, what is with all those "corrects" ) and, if so, how does it rate speed-wise?

Hi Rick! A funny story, before I posted my issue I had tried to come up with the formula myself, but it didn't work. So I posted my issue. Aladin and Eric were kind enough to answer so I was dissecting both formulas to see how they worked. I couldn't get Aladin's to work, and I couldn't reverse-engineer it easily because I didn't understand the mechanics (but I get it now thanks to his explanation). While breaking down Eric's formula, I came down to the original formula I had come up with before. But this time, it worked. I must've had a typo in my earlier formula. I was going to post the solution... but you beat me to it . So yes, it works.
As for speed, the arrays I have tied to the formula are pretty small. I don't notice any slowdown when using it. Is there a way to easily test the calculation speed of a formula in Excel?

Marco, if you're reading this, thank you as well!

 

[Eric W]

I put my original formula in, and wrote a VBA macro to recalculate the sheet 100,000 times. It took roughly 9 seconds. I then put in Rick's, and it took about 8.3 seconds. I was unable to get Aladin's formula to work, but if he'll provide a working version, I'll give that a shot.

Granted, this isn't an extremely foolproof method for a lot of reasons, but it does give a bit of an idea. Same data, same background processes.

 

[Marcelo Branco]

Marco, if you're reading this, thank you as well!

You are welcome.
Well...i think my formula (post #4) also works fine

M.

 

[mrblister]

I put my original formula in, and wrote a VBA macro to recalculate the sheet 100,000 times. It took roughly 9 seconds. I then put in Rick's, and it took about 8.3 seconds. I was unable to get Aladin's formula to work, but if he'll provide a working version, I'll give that a shot.

Actually, I'm curious as well. This spreadsheet may end up becoming huge, and efficiency may become a factor.

By the way, ISNUMBER\MATCH operates generally faster than an evaluation thru SUMIF(S).

Aladin, would it be possible if you could please provide a working version of your formula?

 

[Marcelo Branco]

I put my original formula in, and wrote a VBA macro to recalculate the sheet 100,000 times. It took roughly 9 seconds. I then put in Rick's, and it took about 8.3 seconds. I was unable to get Aladin's formula to work, but if he'll provide a working version, I'll give that a shot.

Granted, this isn't an extremely foolproof method for a lot of reasons, but it does give a bit of an idea. Same data, same background processes.

Eric
In my tests the results were much better (faster)
My computer is not specifically fast: Intel Core i5 2.80 GHz; 8 GB RAM; Win 7; Excel 2010 - 32 bit

The results were
Rick's = 2.60546875
Mine = 2.578125
Eric's = 2.58203125

I used this code (i think it's ok, is it?)

Sub TestFormulas()
    'Thread: http://www.mrexcel.com/forum/excel-questions/981071-can-you-help-me-shorten-formula-2.html
    Dim StartTime As Double
    Dim ElapsedTime1 As Double, ElapsedTime2 As Double, ElapsedTime3 As Double
    Dim i As Long
    
    'Rick's Formula
    StartTime = Timer
    With Range("B3")
        .ClearContents
        .Formula = "=A3+SUMPRODUCT((Sheet1!$B$2:$B$35=J4:O4)*(Sheet1!$N$2:$N$35=E$4)*(Sheet1!$S$2:$S$35)*J5:O5)"
        For i = 1 To 100000
            Application.Calculate
        Next i
        .Value = .Value
    End With
    ElapsedTime1 = Timer - StartTime
    
    'My Formula
    StartTime = Timer
    With Range("B6")
        .ClearContents
        .Formula = "=A3+SUMPRODUCT(IFERROR(INDEX(J5:O5,MATCH(T(IF({1},Sheet1!$B$2:$B$35)),J4:O4,0)),0)," _
            & "--(Sheet1!$N$2:$N$35=E$4),Sheet1!$S$2:$S$35)"
        For i = 1 To 100000
            Application.Calculate
        Next i
        .Value = .Value
    End With
    ElapsedTime2 = Timer - StartTime
    
    'Eric's formula
    StartTime = Timer
    With Range("B9")
        .ClearContents
        .FormulaArray = "=A3+SUM(SUMIFS(Sheet1!$S$2:$S$35,Sheet1!$N$2:$N$35,E$4,Sheet1!$B$2:$B$35,J4:O4)*J5:O5)"
        For i = 1 To 100000
            Application.Calculate
        Next i
        .Value = .Value
    End With
    ElapsedTime3 = Timer - StartTime
    
    Debug.Print "Rick's = " & ElapsedTime1
    Debug.Print "Mine = " & ElapsedTime2
    Debug.Print "Eric's = " & ElapsedTime3
    
    
    'MsgBox "Rick's formula CSE takes: " & ElapsedTime1 & vbCrLf _
           & "mine takes: " & ElapsedTime2 & vbCrLf _
           & "Eric's takes: " & ElapsedTime3
End Sub

M.

 

[Rick Rothstein]

Sub TestFormulas()
    'Thread: http://www.mrexcel.com/forum/excel-questions/981071-can-you-help-me-shorten-formula-2.html
    Dim StartTime As Double
    Dim ElapsedTime1 As Double, ElapsedTime2 As Double, ElapsedTime3 As Double
    Dim i As Long
    
    'Rick's Formula
    StartTime = Timer
    With Range("B3")
        .ClearContents
        .Formula = "=A3+SUMPRODUCT((Sheet1!$B$2:$B$35=J4:O4)*(Sheet1!$N$2:$N$35=E$4)*(Sheet1!$S$2:$S$35)*J5:O5)"
[B][COLOR="#FF0000"]        For i = 1 To 100000
            Application.Calculate
        Next [/COLOR]i[/B]
        .Value = .Value
    End With
    ElapsedTime1 = Timer - StartTime
    
    'My Formula
    StartTime = Timer
    With Range("B6")
        .ClearContents
        .Formula = "=A3+SUMPRODUCT(IFERROR(INDEX(J5:O5,MATCH(T(IF({1},Sheet1!$B$2:$B$35)),J4:O4,0)),0)," _
            & "--(Sheet1!$N$2:$N$35=E$4),Sheet1!$S$2:$S$35)"
[B][COLOR="#FF0000"]        For i = 1 To 100000
            Application.Calculate
        Next[/COLOR] i[/B]
        .Value = .Value
    End With
    ElapsedTime2 = Timer - StartTime
    
    'Eric's formula
    StartTime = Timer
    With Range("B9")
        .ClearContents
        .FormulaArray = "=A3+SUM(SUMIFS(Sheet1!$S$2:$S$35,Sheet1!$N$2:$N$35,E$4,Sheet1!$B$2:$B$35,J4:O4)*J5:O5)"
[B][COLOR="#FF0000"]        For i = 1 To 100000
            Application.Calculate
        Next [/COLOR]i[/B]
        .Value = .Value
    End With
    ElapsedTime3 = Timer - StartTime
    
    Debug.Print "Rick's = " & ElapsedTime1
    Debug.Print "Mine = " & ElapsedTime2
    Debug.Print "Eric's = " & ElapsedTime3
    
    
    'MsgBox "Rick's formula CSE takes: " & ElapsedTime1 & vbCrLf _
           & "mine takes: " & ElapsedTime2 & vbCrLf _
           & "Eric's takes: " & ElapsedTime3
End Sub

M.

Why the highlighted loops?