# Sumproduct or better quicker formula

#### dariushou

##### Board Regular
Hello there,

I'm trying to figure out the best (the quickest for calculations) formula to use for my data set. My data is laid out in the following way:

I have about 30 sections in one worksheet. Each section has 6 scenarios. For example, the first section in the below example is "Average Balance Factor" and it has 6 scenarios accross columns B thru G. The data goes down about 600 rows--each row represents a month so "1" would equal month 1 and so on.

I have a model in another worksheet within the same workbook that looksup the relevant period and scenario that i'm using and uses that data in a calculation. I have a macro that goes through a loop of different loans and each loan could use a different scenario (depending on what scenario i've given to such loan).

I know that i can use the following two formulas (for the "Average Balance Factor as an example")

SUMPRODUCT((Data!\$B\$1:\$G\$1=Model!\$B\$13)*(Data!\$A\$6:\$A\$15=Model!\$A26),Data!\$B\$6:\$G\$15)

With B13 being the scenario for that loan (B13 is populated by the macro)
A26 is just the current period

CSE formula array works as well
SUM(IF((Data!\$B\$1:\$G\$1=Model!\$B\$13)*(Data!\$A\$6:\$A\$15=Model!\$A26),Data!\$B\$6:\$G\$15))

Because the data goes out 600 rows and is now over 180 columns (30 sections with 6 scenarios each) is there a better way than the sumproduct approach. I'm looking for speed here. Thanks,

Excel Workbook
ABCDEFGHIJKLMNOPQRS
1Scenario123456123456123456
2
3
5
61100.00%100.00%100.00%100.00%100.00%100.00%12.00%12.00%12.00%12.00%12.00%12.00%0.15%0.15%0.15%0.15%0.15%0.15%
7299.98%99.98%99.98%99.98%99.98%99.98%12.00%12.00%12.00%12.00%12.00%12.00%0.15%0.15%0.15%0.15%0.15%0.15%
8399.60%99.60%99.60%99.60%99.60%99.60%12.00%12.00%12.00%12.00%12.00%12.00%0.15%0.15%0.15%0.15%0.15%0.15%
9499.55%99.55%99.55%99.55%99.55%99.55%12.00%12.00%12.00%12.00%12.00%12.00%0.15%0.15%0.15%0.15%0.15%0.15%
10599.35%99.35%99.35%99.35%99.35%99.35%12.00%12.00%12.00%12.00%12.00%12.00%0.15%0.15%0.15%0.15%0.15%0.15%
11699.03%99.03%99.03%99.03%99.03%99.03%12.00%12.00%12.00%12.00%12.00%12.00%0.15%0.15%0.15%0.15%0.15%0.15%
12798.83%98.83%98.83%98.83%98.83%98.83%12.00%12.00%12.00%12.00%12.00%12.00%0.15%0.15%0.15%0.15%0.15%0.15%
13898.50%98.50%98.50%98.50%98.50%98.50%12.00%12.00%12.00%12.00%12.00%12.00%0.15%0.15%0.15%0.15%0.15%0.15%
14997.89%97.89%97.89%97.89%97.89%97.89%12.00%12.00%12.00%12.00%12.00%12.00%0.15%0.15%0.15%0.15%0.15%0.15%
151097.43%97.43%97.43%97.43%97.43%97.43%12.00%12.00%12.00%12.00%12.00%12.00%0.15%0.15%0.15%0.15%0.15%0.15%
Data

### Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

#### Fazza

##### MrExcel MVP
HiI,

If I understand correctly, this is a two way lookup. The Excel function for this is INDEX. So, something like below - untested. HTH, Fazza

Code:
``=index(match(Model!\$B\$13,Data!\$B\$1:\$G\$1,0),match(Model!\$A26,Data!\$A\$6:\$A\$15,0))``

#### dariushou

##### Board Regular
Yes, two conditional lookup.

I couldn't get the index/match formula to work, but i did end up coming up with the below vlookup which is much faster than sumproduct. I just hate vlookups as if someone inserts a column then it throws everything off. Not that anyone should be inserting columns--i just like to make my spreadsheets dummy proof.

So for the 3rd section of data ("Premium"), this formula would do the lookup.

VLOOKUP(Model!\$A26,Data!\$A\$6:\$S\$2380,Model!B\$13+1+6*2,FALSE)

This works, but does anyone know of a formula as fast or close to it that doesn't use vlookup. Otherwise, i'll go with the vlookup.

Thanks Again!

#### Fazza

##### MrExcel MVP
In the fresh of the morning it is obvious why the (untested) formula I posted above didn't work - I omitted the range reference! Should be
Code:
``=index(Data!\$B\$6:\$G\$15,match(Model!\$B\$13,Data!\$B\$1:\$G\$1,0),match(Model!\$A26,Data!\$A\$6:\$A\$15,0))``

#### dariushou

##### Board Regular
I still can't get it to work. If i try to look up a value in the first section "Average Balance" under Scenario 1, i get "100%" for periods 1-6 and then "#Ref!" for each period thereafter. I think i'm entering in the formula right, but I'm not to familar with index/match formulas.

#### pgc01

##### MrExcel MVP
Hi dariushou, Fazza

I believe you just have to switch the row and the column:

=index(Data!\$B\$6:\$G\$15,match(Model!\$A26,Data!\$A\$6:\$A\$15,0),match(Model!\$B\$13,Data!\$B\$1:\$G\$1,0))

#### dariushou

##### Board Regular
Code:
``=INDEX(Data!\$B\$6:\$G\$2380,MATCH(Model!\$C\$2,Data!\$B\$1:\$G\$1,0),MATCH(Model!A10,Data!\$A\$6:\$A\$2380,0))``

I moved the reference:

C2 is the scenario and is equal to "1"
A10 is the period in the model and is equal to 7

The model screen has the period in column A with period one in cell A4 and the scenario is in cell C2

So the formula is in column B and i copied it down from cell B4 all the way to B629. For B4 thru B9 i get "100%" and then from B7 to the end i get "#REF!".

Last edited:

#### Fazza

##### MrExcel MVP
Sounds like a good idea, pgc!! I got that really wrong. Thanks

#### dariushou

##### Board Regular
Great. Thanks you two. That was the issue. I need to learn the index/match.

Replies
3
Views
359
Replies
4
Views
351
Replies
0
Views
239
Replies
4
Views
223
Replies
1
Views
115

1,191,582
Messages
5,987,472
Members
440,097
Latest member
Wint

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

### Which adblocker are you using?

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

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