Sum matches from another sheet excluding duplicates

[psycprof]

I have a spreadsheet set up where I have courses that a student has taken, their grades, etc. on one spreadsheet and a list of gen ed's on another sheet. Example:

Sheet 1

[TABLE="width: 500"]
<tbody>[TR]
[TD]Course[/TD]
[TD]Grade Pts[/TD]
[TD]Credits[/TD]
[/TR]
[TR]
[TD]PSY 101[/TD]
[TD]2[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]SOC 101[/TD]
[TD]4[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]BIO 101[/TD]
[TD]3[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]JUS 101[/TD]
[TD].33[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]JUS 101[/TD]
[TD]1[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]PSY 101[/TD]
[TD]4[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]PSY 301[/TD]
[TD]4[/TD]
[TD]4[/TD]
[/TR]
</tbody>[/TABLE]


Sheet 2
[TABLE="width: 500"]
<tbody>[TR]
[TD]Gen Ed Courses[/TD]
[/TR]
[TR]
[TD]PSY 101
[/TD]
[/TR]
[TR]
[TD]SOC 101[/TD]
[/TR]
[TR]
[TD]BIO 101[/TD]
[/TR]
[TR]
[TD]JUS 101[/TD]
[/TR]
</tbody>[/TABLE]


What I need my formula to do is count the credits only from courses that receive above .67 in grade points (column B in spreadsheet 1) and where the course in column A in spreadsheet 1 matches a course in spreadsheet 2. I also need to make sure courses are not counted more than once. So from this example, I should have 12 credits because PSY 101, SOC 101, BIO 101, and JUS 101 have all been passed with above .67 in spreadsheet 1 and appear on spreadsheet 2. PSY 301 should not be counted because it is not on spreadsheet 2. PSY 101 and JUS 101 should only be counted once because they are duplicated (and JUS 101 was failed the first time). Oh, I also need the formula to handle zeroes since the number of courses completed by each student is variable (it would go from A4:A100) for everyone. I'd appreciate any help anyone could provide!

 

[cyrilbrd]

Hi,
Not sure if this is what you wanted:
Given in Sheet1 cell A1

Excel 2010
	A	B	C	D
1	Course	Grade Pts	Credits	helper
2	PSY 101	2	3	1
3	SOC 101	4	3	1
4	BIO 101	3	3	1
5	JUS 101	0.33	3	0
6	JUS 101	1	3	1
7	PSY 101	4	3	2
8	PSY 301	4	4	1
Sheet1

Add one helper column with in D2 =COUNTIFS($B$2:B2,">=1",$A$2:A2,A2) copied down till needed.
NOTE: this column can be hidden.

Given in Sheet2 cell A1

Excel 2010
	A	B
1	Gen Ed Courses
2	PSY 101	3
3	SOC 101	3
4	BIO 101	3
5	JUS 101	3
Sheet2

Formula in B2 is =SUMIFS(Sheet1!$C$2:$C$8,Sheet1!$A$2:$A$8,Sheet2!A2,Sheet1!$B$2:$B$8,">=1",Sheet1!$D$2:$D$8,"=1")

Would that be what you needed?

 

[psycprof]

Does that formula handle duplicate values or blank cells? It does not appear to handle either. I need to make sure each course is only counted once upon passing (sometimes students retake courses for higher grades even if they passed it the first time).

 

[cyrilbrd]

Does that formula handle duplicate values or blank cells? It does not appear to handle either. I need to make sure each course is only counted once upon passing (sometimes students retake courses for higher grades even if they passed it the first time).

Kindly be advised that based on your sample posted in thread#1 and re-posted in thread#2, the duplicates are handled as required. Does it not work as you required? The helper column in sheet1 will identify all duplicates. hence will count occurrences. The second formula in Sheet2 will take into consideration the duplicates from sheet1 as well as the value in ColumnB (Grade Pts). Would you mind posting a new short sample with blank cell including the expected results?
Thanks.