slow SUMPRODUCT formula

[justinkim12]

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!

 

[kmillen]

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

 

[Aladin Akyurek]

Only a slight improvement expected...

=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]

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]

Thanks for the additional information about your question. I set up a mock table in an attempt to duplicate your issue.

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]

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]

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

Just keep adding conditionals.

(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]

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

Thanks for your help!