Sumproduct too slow

[dan]

Hi all:

I'm trying to analyze survey data in excel and trying to avoid using pivo
tables. The results are each survey is one row of data, with location, month
and then 13 questions, all fields are numeric.

I'm looking to basically count the number of each reponse, by location, b
month, by question. For example for Q1 the answers are between 0 and 4 (ver
good, good, neutral, bad, very bad scale).

I'm trying to get it to show me for location 1, for month 1, for question 1:

Very good = 5
good = 4
neutral = 7
bad = 9
very bad = 10

I'm currently using sumproduct to check the location, month, and response. O
course, this involve having several hundred fields of sumproduct formulas and i
takes hours / crashes excel.

I realize excel isn't the ideal platform for this, but as the data is entere
at each location (15 locations) it needs to be written in excel.

Any suggestions? I can email an example of the spreadsheet if that helps.

 

[dan]

dan wrote on 03/10/2010 17:00 ET :

Hi all:

I'm trying to analyze survey data in excel and trying to avoid using pivot
tables. The results are each survey is one row of data, with location month,
and then 13 questions, all fields are numeric.

I'm looking to basically count the number of each reponse, by location, by
month, by question. For example for Q1 the answers are between 0 and 4 (very
good, good, neutral, bad, very bad scale).

I'm trying to get it to show me for location 1, for month 1, for question 1:

Very good = 5
good = 4
neutral = 7
bad = 9
very bad = 10

I'm currently using sumproduct to check the location, month, and response Of
course, this involve having several hundred fields of sumproduct formula and
it takes hours / crashes excel.

I realize excel isn't the ideal platform for this, but as the data i entered
at each location (15 locations) it needs to be written in excel.

Any suggestions? I can email an example of the spreadsheet if that helps.

Sorry, I meant to include the sumproduct formula I'm using:

=SUMPRODUCT(--(Data!$D$5:$D$60001=$B$3),--(Data!$A$5:$A$60001=C$6),--(Data!$E$5:$E$60001=$B7))

Where B3 is the location # (user-defined), B7 (and then further down the
column) is the number value of the response (very good = 4, good=3, etc) and C
is the month.

This is what it looks like...F is every cell the formula is in...

B C D E
3 1
4
5
6 1 2 3 4
7 4 F F F F
8 3 F F F F
9 2 F F F F
10 1 F F F F