Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Countif layering
I have a data file of three columns: one is a subject (math, art, etc.), the
second contains a code for the first student to get it right (AA, e.g.) and the students that got it wrong (AA, e.g.). My data looks something like this, but much longer: A B C Math AA AE Sci AB none Lit BE BA Art BC AA, AC Math AD AE I want to compute the number of right and wrong for each cubject for each student, in a sheet like this one: Math R Math W Art R Art W..... AA x y AB AC AD BA What is the best way to calculate these numbers? What combination of functions should I use for x and y? Thanks in advance. |
#2
|
|||
|
|||
With multiple entries in a single cell in columns B & C, you've got a mess to
deal with. If the row you have shown as Art BC AA, AC was broken into 2 rows Art BC AA Art AC and the same for all other rows where you have multiple codes in a single cell, you could get your desired results VERY, VERY EASILY with pivot table. Short of that, I'm not sure how you could get from where you are to an accurate result like what you want. "Ben Blair" wrote: I have a data file of three columns: one is a subject (math, art, etc.), the second contains a code for the first student to get it right (AA, e.g.) and the students that got it wrong (AA, e.g.). My data looks something like this, but much longer: A B C Math AA AE Sci AB none Lit BE BA Art BC AA, AC Math AD AE I want to compute the number of right and wrong for each cubject for each student, in a sheet like this one: Math R Math W Art R Art W..... AA x y AB AC AD BA What is the best way to calculate these numbers? What combination of functions should I use for x and y? Thanks in advance. |
#3
|
|||
|
|||
The right answers ones are pretty easy
if your totals table is in sheet2 Manes in Column A and courses in the first row Assuminc Column B is Math R enter in B2 =sumproduct(--(Sheet1!$B$1:$B$1000=$A2),--(Sheet1!$A$1:$A$1000="Math")) Copy to the Art R and Lit R columns changing "Math" to "Art" and "Lit" as appropriate. Copy this down to the end of your student list since there can be multiples in the Wrong column this equaiton gets a little more complex If you can change the wrong column into separate cells for each wrong person Look at Data-Text to Columns with Comma delimiter for one method enter in the cell under the Math W cell =sumproduct( --(Sheet1!$c$1:$c$1000=$A2) --(Sheet1!$D$1:$D$1000=$A2)--(Sheet1!$E$1:$E$1000=$A2),--(Sheet1!$A$1:$A$1000="Math")) *Add as many sections in first part as is needed.* or =sumproduct(--(if(iserror(find($A2, Sheet1!,$C$2:$C$1000),0,1),--(Sheet1!$A$1:$A$1000="Math")) Copy these to the LIT W and Art W columns Making appropriate changes and copy for your student list. "Ben Blair" wrote: I have a data file of three columns: one is a subject (math, art, etc.), the second contains a code for the first student to get it right (AA, e.g.) and the students that got it wrong (AA, e.g.). My data looks something like this, but much longer: A B C Math AA AE Sci AB none Lit BE BA Art BC AA, AC Math AD AE I want to compute the number of right and wrong for each cubject for each student, in a sheet like this one: Math R Math W Art R Art W..... AA x y AB AC AD BA What is the best way to calculate these numbers? What combination of functions should I use for x and y? Thanks in advance. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
COUNTIF COMBINATION?? | Excel Worksheet Functions | |||
Combining IF and COUNTIF based on two columns | Excel Discussion (Misc queries) | |||
COUNTIF in one colum then COUNTIF in another...??? | Excel Worksheet Functions | |||
Countif - Countif | Excel Worksheet Functions | |||
countif, again | Excel Worksheet Functions |