At this stage, I know things about this dataset that you don't.  While you were watching the Australian Wallabies crush the British Lions by one point in a rugby match, I was scrolling through the Data worksheet.  I sensed that it will be useful to add a field to the Data worksheet which counts the number of test items that a student did not answer.  This means I want to sum the number of 9s found for each student in Data columns 5 through 28.  To do this I can use a special Excel function, "COUNTIF", or I can use Lertap.  In this topic I'll use COUNTIF.

 

The first student's results are found in row 3, column 5 of the Data worksheet.  Now, I know that the COUNTIF function is one of many in Excel that likes to have letters in the column headers instead of numbers (this is not strictly correct, COUNTIF will also be happy using "rc" notation, an example is seen below).  For example, the first column will be "A", not "1".  Lertap much prefers to have numbers as column headers and it'll almost always try to put them there.  How to change column headers to letters instead so that I will be able to COUNTIF?

 

Most easy.  I make use of a Lertap "Excel shortcut", the one called "Ref. style".  Once I've done this, the column headers become letters, and I see that item responses, formerly found in columns 5 through 28, are now in columns E through AB.

 

I've taken another screen snapshot, hiding some of the columns so that you can see for yourself.  The responses for the first item, I1, are in column E, while the responses for the last item, I24, are in column AB, see:

 

 

Once I have letters as the column headers, I am poised to use Excel's COUNTIF function, and a little beauty it is.

 

 

In cell AC3, that is, in column AC, row 3, I have entered a formula which will count the number of 9s for the first student, as found in cells E3 through AB3: =COUNTIF(E3:AB3,"9")

 

 

A reader wrote in to say that he'd suggest using "rc" notation instead of having to switch column headers to letters.  I show this in the snapshot above.  ("rc" stands for "row-column"; if there's no number after the R, or after the C, that tells Excel to apply the formula to the whole row, or to the whole column.)

 

To have my formula copied to all following rows is easy: see the yellow highlight above?  See the little black square that's in the yellow highlight?  A double-click on that little black square will see that my formula is copied to all following rows.

 

Yes, I know, you're sitting there in seat 751U of your Airbus 380 flight to Waukesha, and wondering why oh why have I done this.  You'll see two topics down.  Let me first show you how you could get Lertap to count the number of 9s.  I will do so in the next topic.