+ Reply to Thread
Results 1 to 3 of 3

Count the number of matches in a row of one word answers with a row of correct answers

  1. #1
    Registered User
    Join Date
    04-06-2012
    Location
    San Jose, CA
    MS-Off Ver
    Excel in Office 2011 for Mac
    Posts
    7

    Count the number of matches in a row of one word answers with a row of correct answers

    I use the COUNTIF function to count the number of correct answers (words) for each test item, using this:
    = COUNTIF(B5:B35,B1) where the 30 cell range is for the one-word answers in a column for one item, with each answer counted only if it matches the corresponding word in the key at the top of the column (beginning at B1) . When copied for each item (each vertical column), the range changes appropriately for each shift to the right, and the key word cell also shifts one cell to the right. This works fine for vertical comparisons.

    However, when I try to us a similar formula for counting correct answers horizontally (in a row) for each student’s score (where each cell is compared to the corresponding cell in the key running across the top of the page, and only correct answers are to be counted), it seems to compare each answer with only the first cell in the key series.

    Any idea for a formula that will give me scores (no of correct answers) for each student? Or is there a way that columns and rows can be exchanged (PivotTable doesn’t seem to do this)? I’d prefer not to exchange rows and columns.
    Thanks

  2. #2
    Forum Expert
    Join Date
    09-27-2011
    Location
    Poland
    MS-Off Ver
    Excel 2007
    Posts
    1,312

    Re: Count the number of matches in a row of one word answers with a row of correct answers

    Hi Could You upload sample workbook. It looks like You need proper use of $ in your formulas.
    Regards

    tom1977

    If You are satisfied with my solution click the small star icon on the left to say thanks.

  3. #3
    Registered User
    Join Date
    04-06-2012
    Location
    San Jose, CA
    MS-Off Ver
    Excel in Office 2011 for Mac
    Posts
    7

    Re: Count the number of matches in a row of one word answers with a row of correct answers

    tom1977,
    Thanks. I thought I had figured it out, but forgot the $ to fix the row of answers in key for each column. I've done that, and my solution follows (inelegant, but it works). Seems like there should be a simpler function for that.
    =COUNTIF(K7,K$3)+COUNTIF(L7,L$3)+COUNTIF(M7,M$3)+... (etc., for total of 40 items to be added).

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1