Hi, Dave. Thank you so much for your help. I consider myself pretty
proficient in Excel, but the SUMPRODUCT formula is completely new to me. I'm
excited about the possibilities.

I'm not sure what I'm doing wrong, but I can't get the formula to return the
correct number. As an example: rows A2:A26 contain records for a single
student. (We'll call him John.) There are multiple rows for each student
because each row represents a course that student has taken. Column A
contains the student name. Column F contains the course grade. I entered
the SUMPRODUCT formula into Column G, as you suggested. The result returned
was '17', but that is not correct. Looking at John's grades, he actually has
failed 7 courses. (Obviously not our best student!)

I don't know if it makes a difference, but I feel I should mention that my
data represents each student's entire course history, so the file is quite
large - over 30,000 rows.

Do you have any idea what I'm doing wrong?

Thanks again for your EXCELLENT help!

Julie



"Dave Peterson" wrote:

Maybe you could add another column "Courses Failed" to the raw data.

If stuname is in column A and grade is in column F, then a formula like this in
G2 (nice header in G1):

=sumproduct(--(A2:A999=a2),--(F2:F999<75))

Adjust the ranges to match--but you can't use whole columns (except in xl2007+).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

=======
Then you can use this new field as a page field and hide the records that are
less than 2.

(or modify the formula in G2:
=sumproduct(--(A2:A999=a2),--(F2:F999<75))1

This will return True if the count is more than one.

========
When you move to xl2007+, you'll be able to use an =countifs() formula that can
check more than one column.



KingdomGirl wrote:

I'm using Excel 2003. I'm a high school guidance secretary. The
administrators are constantly asking for data relating to course failures.
From our school's student management program, I've exported a listing of all
course grades for the year and used the data to create a pivot table.

Right now, I'm only using row fields: StuName, GradeLVL, TermID, StoreCode,
Course, Grade

I've filtered the data to only show records with a TermID of '1901' &
'1900'. (Semester 1 Final or Year Long). I've also filtered the StoreCode to
only show records with a StoreCode of 'S1'. I've filtered to Grade data to
only show records with a grade < 75. (Course Failures)

My delima is that I need to show ONLY records for students with two or more
course failures. Currently, I'm sorting through the records and hiding the
rows for students with only one course failure. It seems like there should
be a way for Excel to calculate this data for me, but I don't know how in the
Pivot Table.

Can you help?

Thanks!

--

Dave Peterson
.