Sumproduct
 

I have the following tables:

WEEKS:

COLUMN HEADERS:
W/C DATE
MON AM
MON PM
TUE AM
TUE PM
ETC. TIL FRI PM
USERID


MON AM-Fri PM contain different reason codes.

STUDENT TABLE:

STUDENTID
SURNAME
FIRSTNAME



studentID = UserID in weeks table

CODE COUNTING:

CODE COUNTING IS A TABLE WITH ID NUMBERS IN COLUMN B AND CODES ACROSS
ROW 5.


cell C6 contains the formula:

=SUMPRODUCT(--(Weeks!$M$1:$M$10=$B6),--(Weeks!$B$1:$B$10=C$5))+SUMPRODUCT(--(Weeks!$M$1:$M$10=$B6),--(Weeks!$C$1:$C$10=C$5))+SUMPRODUCT(--(Weeks!$M$1:$M$10=$B6),--(Weeks!$D$1:$D$10=C$5))+SUMPRODUCT(--(Weeks!$M$1:$M$10=$B6),--(Weeks!$E$1:$E$10=C$5))+SUMPRODUCT(--(Weeks!$M$1:$M$10=$B6),--(Weeks!$F$1:$F$10=C$5))+SUMPRODUCT(--(Weeks!$M$1:$M$10=$B6),--(Weeks!$G$1:$G$10=C$5))+SUMPRODUCT(--(Weeks!$M$1:$M$10=$B6),--(Weeks!$H$1:$H$10=C$5))+SUMPRODUCT(--(Weeks!$M$1:$M$10=$B6),--(Weeks!$I$1:$I$10=C$5))+SUMPRODUCT(--(Weeks!$M$1:$M$10=$B6),--(Weeks!$J$1:$J$10=C$5))+SUMPRODUCT(--(Weeks!$M$1:$M$10=$B6),--(Weeks!$K$1:$K$10=C$5))

which counts all the occurrences of code in c5, through all the Weeks
records where the USERID matches Student ID.

Is there another formula I can use, that is quicker, for dealing with
large numbers of records. (Going to be about 18000 records by the end
of the year).

Or is it possible to do this in an Access database quicker?

ty in advance for any help


--
masterkeys

Sumproduct
 

Can be shortened to...

=SUMPRODUCT((Weeks!$M$1:$M$10=$B6)*(Weeks!$B$1:$K$ 10=C$5))

Hope this helps!

In article ,
masterkeys wrote:

I have the following tables:

WEEKS:

COLUMN HEADERS:
W/C DATE
MON AM
MON PM
TUE AM
TUE PM
ETC. TIL FRI PM
USERID


MON AM-Fri PM contain different reason codes.

STUDENT TABLE:

STUDENTID
SURNAME
FIRSTNAME



studentID = UserID in weeks table

CODE COUNTING:

CODE COUNTING IS A TABLE WITH ID NUMBERS IN COLUMN B AND CODES ACROSS
ROW 5.


cell C6 contains the formula:

=SUMPRODUCT(--(Weeks!$M$1:$M$10=$B6),--(Weeks!$B$1:$B$10=C$5))+SUMPRODUCT(--(W
eeks!$M$1:$M$10=$B6),--(Weeks!$C$1:$C$10=C$5))+SUMPRODUCT(--(Weeks!$M$1:$M$10=
$B6),--(Weeks!$D$1:$D$10=C$5))+SUMPRODUCT(--(Weeks!$M$1:$M$10=$B6),--(Weeks!$E
$1:$E$10=C$5))+SUMPRODUCT(--(Weeks!$M$1:$M$10=$B6),--(Weeks!$F$1:$F$10=C$5))+S
UMPRODUCT(--(Weeks!$M$1:$M$10=$B6),--(Weeks!$G$1:$G$10=C$5))+SUMPRODUCT(--(Wee
ks!$M$1:$M$10=$B6),--(Weeks!$H$1:$H$10=C$5))+SUMPRODUCT(--(Weeks!$M$1:$M$10=$B
6),--(Weeks!$I$1:$I$10=C$5))+SUMPRODUCT(--(Weeks!$M$1:$M$10=$B6),--(Weeks!$J$1
:$J$10=C$5))+SUMPRODUCT(--(Weeks!$M$1:$M$10=$B6),--(Weeks!$K$1:$K$10=C$5))

which counts all the occurrences of code in c5, through all the Weeks
records where the USERID matches Student ID.

Is there another formula I can use, that is quicker, for dealing with
large numbers of records. (Going to be about 18000 records by the end
of the year).

Or is it possible to do this in an Access database quicker?

ty in advance for any help

Sumproduct
 

Domenic Wrote:
Can be shortened to...

=SUMPRODUCT((Weeks!$M$1:$M$10=$B6)*(Weeks!$B$1:$K$ 10=C$5))

Hope this helps!


Ty this helped a lot. Sped the processing of it right up.


--
masterkeys