# slow SUMPRODUCT formula

#### justinkim12

##### New Member
I have the following formula in my spreadsheet, and it seems to be slowing the spreadsheet considerably:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(I\$94:I\$551,ROW(I\$94:I\$551)
-MIN(ROW(I\$94:I\$551)),,1))*(I\$94:I\$551=\$A30))

Here is what I am trying to do:

I have several columns of numbers. I want to count the number of times a particular numerical value shows up in each column. I want to be able to filter the data and have this formula display the number of times a particular numerical value shows up for the filtered values only.

I copy and paste the above formula across rows and columns to cover all columns I want. In the above formula, I am referencing data in column I, and comparing it to the value in cell A30.

The formula works, but it really slows down the spreadsheet. In my current spreadsheet, I have 20 rows and about 140 columns with this formula which references about 460 rows of data across those 140 columns.

Is there a way write the formula more efficiently or perhaps break apart the formula into smaller pieces? I don't mind copying and pasting sets of formulas.

Thanks for your help. I'm really stumped on this!

Last edited:

### Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

#### kmillen

##### Board Regular
=COUNTIF(\$I\$94:\$I\$551, A30)

Assuming A30 has the filtered value you're checking for comparison, the above method yields the same results as your formula.

Some sample data from your sheet might help me better understand your issue.

##### MrExcel MVP
Only a slight improvement expected...
Code:
``````=SUMPRODUCT(
SUBTOTAL(2,OFFSET(I\$94,ROW(I\$94:I\$551)-ROW(I\$94),,1)),
--(I\$94:I\$551=\$A30))``````

Are there any other formulas with volatile functions like OFFSET, INDIRECT, etc. or many lookup formuals with match-type set to 0?

#### justinkim12

##### New Member
Thanks for the replies. The COUNTIF function is close, but it still counts all values, even when I am filtering.

Here is what the data looks like:

Let's say I have 100 rows of survey data, each row representing survey responses from a different person. Each respondent indicates whether they live in the East, Central or West. These values are represented in column B. They then answer how satisfied they are with a particular product, on a five point scale (1 through 5). These values are in column C.

What I would like to know is: of the people living in each of the three regions, how many gave the 1,2,3,4 and 5 satisfaction responses?

So, currently, I have the data starting in row 10, and that sumproduct formula above the values in column C. In column A, rows 1-5, I have the numeric values, 1-5, that I am trying to find. Then I autofilter the columns, and manually go through each of the three regions to find the number of instances in column C.

Obviously, this is a simplified example. There are more than 100 responses, more than 1 question and more than 1 sort column. The sumproduct formula I have is nice in that I can copy across and down as many cells I need and I can use the formula for a number of survey analyses. The problem is that it's now very slow.

#### kmillen

##### Board Regular

I created 100 random respondents in the fashion indicated below and hopefully corresponding to your model. (Actual data on my mock up are in rows 2 through 101)

A B C
Respondent Live IN Satisfied (1-5)
1 East 1
2 East 5
3 East 5
4 West 4
5 East 3
6 West 3
7 Central 5
8 West 5
9 West 2
10 East 2
11 Central 4
12 Central 3
...

I then set up an area to accumulate results.

live-in categories
|
\/
E F G H I J <= columns
1 2 3 4 5 <= satisfaction responses

East 4 10 12 4 8
Central 6 6 5 2 7
West 10 11 3 4 8

The data was accumulated using array formulas (I entered the formula below in cell F2 and pressed Ctrl-Shift Enter).

=SUM((\$B\$2:\$B\$101=\$E2) * (\$C\$2:\$C\$101=F\$1))

I then used the drag handle for cell F2 and dragged the formula over to J2 and down to J4.
For each of the three regions, we can see how many respondents gave the 1,2,3,4 and 5 satisfaction response.

Hope this helps

P.S. I see that formatting is not preserved so my table emulations will not align.

#### justinkim12

##### New Member
That would probably work for a limited set of data, but what if I had a number of different variables? For example, in addition to region, suppose there is also gender, income and household size. In the original, I would just set the autofilters and go through the combinations I wanted. Also, is there any way to do this by simply copying and pasting formulas instead of working with arrays?

And by the way, thanks for all of your input into this!

#### kmillen

##### Board Regular
Actually, this method will work for as many variables as you need. It is essentially, SUMIF() on steroids.

Additional criteria may be considered by adding to the SUM() formula. For instance,
suppose columns D through F had gender, income and household size information respectively. The formula could be modify as such.

=SUM((\$B\$2:\$B\$101=\$E2) * (\$C\$2:\$C\$101=F\$1) * (\$D\$2:\$D\$101="Male") * (\$E\$2:\$E\$101 >= 50000) * (\$F\$2:\$F\$101=4))

(Of course in my mock up, the data collection area would have to be moved to accommodate this new data.)

While acknowledging that I may not completely understand the complexities of your issue, did you notice that this method returns the correct results without cutting, pasting and filtering?

Array formulas are the real power in Excel; I'd encourage you to consider them.

I wish I could have been more helpful.
Kerry

#### justinkim12

##### New Member
Great! This looks like a good solution. I will play around with those array formulas. I will admit that arrays are one area I haven't spent too much time with in Excel (though I'm fairly competent with Pivots, tables and even a little VBA).

Replies
10
Views
84
Replies
7
Views
120
Replies
0
Views
93
Replies
5
Views
170
Replies
2
Views
305

1,190,916
Messages
5,983,572
Members
439,850
Latest member
suhailrocks786

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