Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count a range that matches the heading
I have a spreadsheet that is tracking attendance and grades for a class. For
instance, column a has a list of names of people in the class; column d keeps track of the attendance for 6/11, column e keeps track of the grade for 6/11. column f keeps track of the attendance for 6/18; column g keeps track of the grade for 6/18, etc. In order words, there are two columns per week. I am using a sumif formula for the attendance that looks like =sumif($d$6:$k$6,b$6,$d7:$k7) and that is working very well. However, Im having problems with the grades. Not everyone takes a test every week. I want to be able to count the number of test scores for any given person €“ some may have 2, some 5, some 10, etc. How do I get a count of tests taken for each person? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count a range that matches the heading
So, sounds like you want to get a count of every other cell in the row that
contains a test grade (number?). If there aren't too many cells involved the easiest way is: =COUNT(E2,G2,I2,K2) If there are a lot of cells involved try this: =SUMPRODUCT(--(MOD(COLUMN(E2:K2)-COLUMN(E2),2)=0),--(E2:K20)) This does the exact same thing as the above COUNT formula. I'm assuming that if a person does not take a test for a particular date then that cell is left empty and the grades will be positive numbers greater than 0. -- Biff Microsoft Excel MVP "JanetP" wrote in message ... I have a spreadsheet that is tracking attendance and grades for a class. For instance, column a has a list of names of people in the class; column d keeps track of the attendance for 6/11, column e keeps track of the grade for 6/11. column f keeps track of the attendance for 6/18; column g keeps track of the grade for 6/18, etc. In order words, there are two columns per week. I am using a sumif formula for the attendance that looks like =sumif($d$6:$k$6,b$6,$d7:$k7) and that is working very well. However, I'm having problems with the grades. Not everyone takes a test every week. I want to be able to count the number of test scores for any given person - some may have 2, some 5, some 10, etc. How do I get a count of tests taken for each person? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count a range that matches the heading
Hi,
What sort of data do you use to mark attendance? (text or number) What sort of data do you use to enter grades? (text or number) What do you mean by "count the number of test scores" Do you mean count 1 for each test, regardless of grade? Regards - Dave. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
count each row for total matches | New Users to Excel | |||
Compare columns, count matches | Excel Worksheet Functions | |||
formula to sort a range so that it matches the exact rows of a column that is outside that range? | Excel Discussion (Misc queries) | |||
count the # of entries when sum matches the target | Excel Worksheet Functions | |||
COUNT INFORMATION IN COLUMN B ONLY IF A1 MATCHES K1 COUNT THAT CE. | Excel Worksheet Functions |