Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumproduct | Excel Worksheet Functions | |||
sumproduct causing memory errors? | Excel Worksheet Functions | |||
Can I reference =, <, or > sign in SUMPRODUCT | Excel Discussion (Misc queries) | |||
Sumproduct function not working | Excel Worksheet Functions | |||
adding two sumproduct formulas together | Excel Worksheet Functions |