MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Print total of line entries at end

Posted by Mary on August 25, 2001 8:45 PM

I have a report that requires 1 line of data to be entered for each student that will take a class. At the end of the report I need to get a total of the number of students signed up - this will be equal to the number of lines that I have entered (since each student will have 1 line of data). Does anyone know a simple way that I can get this total?

Posted by Tom Urtis on August 25, 2001 9:29 PM


You might consider using the COUNTA function to be useful in this case, which counts all non-blank cells in a range.

Example: If your report's field headers are in row 1, and your report displays 30 students (occupying rows 2 through 31), then in cell A32 enter the formula =COUNTA(A2:A31). Assuming column A is a field in your report that contains one piece of data on every student and would never be blank (such as "Last Name"), then this formula would produce the number 30 in cell A32.

One tip, someone looking at the report would just see the number 30 and wonder "30 of what?". Answer that question ahead of time by nesting the COUNTA function in a concatenated formula in cell A32 as follows:
=CONCATENATE(COUNTA(A2:A31)&" students in this course"). Note the space in between the first quote mark and the word students, which allows for better readability.

Let me know if this helps, or if you are looking for something more.

Tom Urtis

Posted by Mary on August 26, 2001 6:17 AM

Yes - that works great! Thanks much for the help. Mary