Can Excel Count Referencing Cells Not A Fixed Value??? 2

[b1kerch1ck]

Hi,
This is driving me nuts. Sorry if it's a repeat question but I can't find this one in the forum.

I am grading exams. I have two columns, one with the correct answer and one with the answer on the exam. It's a multiple choice test so the answers are just letters.

At the bottom of the second column, I want a count of correct answers. This should be easy...and I thought CountIf would do it, but it seems to need a fixed value to reference, and can't look at other cells.

Is there a function in Excel that will count cells whose value equals other cells in the same row? Thanks!!!

 

[Zathras]

I'm sure there are several ways to do this. Here is a way to do it with an "array formula":

Sample data are in A1:B12

paste this into the formula bar:
[tt]
=SUM(IF(A1:A12=B1:B12,1,0))
[/tt]
and press Ctrl+Shift+Enter. Excel will display the formula inside curly braces: { } indicating that it is an array formula. (Look up "array formula" in the help file.)

 

[SkipVought]

Hi,
[tt]
=SUMPRODUCT((A1:A100=B1:B100)*1)
[/tt]


Skip,
[sub]
[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken! [/sub]

 

[b1kerch1ck]

you rock!
thank you!

 

[SkipVought]

check out...

How can I rename a table as it changes size faq68-1331

If you use this expression in Insert/Name/Define
[tt]
Name: rCorrect
Refers To: =OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A)-1,1)

Name: rStudent
Refers To: =OFFSET(Sheet1!$A$2,0,1,COUNTA(Sheet1!$A:$A)-1,1)
[/tt]
then on your sheet no matter how many questions are recorded for the test, here's the grade...
[tt]
Grade: =SUMPRODUCT((rCorrect=rStudent)*1)/COUNTA(rCorrect)
[/tt]



Skip,
[sub]
[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken! [/sub]