I have a table in Excel 2003 with 46,000 rows, which is created by querying three SQL tables.
I'm using this formula in colum O to calculate the number of records in each Program (C:C)
=SUMPRODUCT(($B$2:$B$65000
=B2)*($C$2
:$C$65000=
C2)*($G$2:
$G$65000=G
2))
B:B may contain one of three Centers, say, "North", "Central" or "South"
C:C may contain one of three Programs, say, "WTP", "WIA" or "WP"
G:G contains the column name representing an audit question like "ActionCaseNotesEntered" for example and there are about 50 different questions in the "WTP" group, about 45 different and unique questions for the "WIA" group and about 35 different questons in the "WP" group. Each group has its own different set of questions.
Looking at the table you'd see a ReviewID in A:A (this is probably the key to the solution to my problem)
Since there are 50 questions in the WTP group the first 50 ReviewIDs in this table are all the same because there is a row for each question. So, the first 50 A:A are the same, say, "3012" and the first 50 B:B are the same also since it's all the same Review and the first 50 C:C are the same, but each of the first 50 G:G are unique: the questions.
The next 50 rows are the same deal, A:A may be "3013", the next ReviewID and then a row for each question.
The idea with the formula was to count the number of occurrences of each question in each Program at each Center to get the number of cases/records/ReviewIDs.
Another way to put the question is, "How many cases (or records or ReviewIDs) in the WTP Program at the Central Office.
Now, that formula works and gives me the numbers I need. I get "153" in column O:O in every row where B:B = "South" and C:C = "WTP".
The problem is it takes 45 minutes to calculate that number in column O:O for 46,000 rows.
Is there a beter way to do this that would be faster? I'm thinking I should use the ReviewID in A:A instead of the question in G:G since the calculation should be simpler. The count of unique ReviewIDs in each group is all I need. Using the question, I have to count the number of times the question occurs in each group and it does seem awkward now.
I hope I haven't explained it too poorly.
Thank you for your help.
