SumProduct column

  • Thread starter Thread starter Avi
  • Start date Start date
A

Avi

I am trying to create a report in excel and have run into a stump.
Potentially the rows could range anywhere from 1 row to 15000 rows.
There are two data columns a I download a report from. Column A is
the course title(there are about 50 different course titles). Column
B represents (passed, complete, incomplete, or NA). A "NA" represents
not attempted.

I am trying to create a report that will show all the different
courses. Each course will have four columns (passed, complete,
incomplete, or NA). What I would like for excel to do is search
Column A and when it runs into a particular course name it references
it with the same row on Column B. It then counts and returns the
value.
When the information is downloaded the information comes randomized in
terms of the course names.

Any help would be great

It would look something like this:

Education Course ______Passed______Complete_______Incomplete_____NA
Course 1 5 3 8 9
Course 2 4 18 15 10
 
Okay, begin your course listings in A2, down, Then grade in beginning i
B2 down. Then put the unique course names beginning in C2 and down
Then put the following in:

D1 => Passed
E1 => Complete
F1 => Incomplete
G1 => NA


Then in D2 put this formula:

=SUMPRODUCT(($A$2:$A$15=$C2)*($B$2:$B$15=D$1))

Copy it down to how many courses are listed in column C, and acros
columns D, E, F, G.

BTW, A15 was the bottom of the list I used. Use whatever row number i
the bottom of the list. (If it will change, consider using dynami
named range).
 
Back
Top