#1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
masterkeys
 
Posts: n/a
Default 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
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Domenic
 
Posts: n/a
Default 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

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
masterkeys
 
Posts: n/a
Default 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
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Sumproduct Peter B Excel Worksheet Functions 1 March 7th 05 02:59 PM
sumproduct causing memory errors? dave Excel Worksheet Functions 1 March 3rd 05 10:31 AM
Can I reference =, <, or > sign in SUMPRODUCT BobT Excel Discussion (Misc queries) 7 February 16th 05 02:58 PM
Sumproduct function not working Scott Summerlin Excel Worksheet Functions 12 December 4th 04 06:15 AM
adding two sumproduct formulas together ski2004_2005 Excel Worksheet Functions 1 November 12th 04 10:08 PM


All times are GMT +1. The time now is 05:48 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"