Sum Column Values if Matching Values Exist In Another Column, excluding first occurence

[mrscottjr]

Hi all,

Hoping you can help. I'm in need of a formula to search if a cell's value is a duplicate, triplicate, etc within a column. If multiples are found, I want to sum the values from another column in those rows - excluding the first occurence.

In my example below, I'm trying to find a formula for the "Retaken Credits" column. I'd like to see if the value in the "Course Code" is found more than once in the "Course Code" column. If it is, I'd like to sum the number of credits but not include the initial attempt's course credits, as I only want to have the "retaken credits" appear in that column. In the example, you can see that MA 101 was taken twice meaning only 4 credits were "retaken" (as opposed to the sum of all MA 101 credits being 8). For LA 101, you can see that it was taken 3 times - therefore I want it to show 6 credits in the retaken column (as opposed to the actual sum of all attempts being 9 credits.) The next step for me is creating two cells somewhere else on the spreadsheet that will show the "Total Credits Taken" (which would be 27 in the example) and a "Retaken Credits Only" value (which would be 10 in the example)

Book1
	A	B	C	D	E
1	Course	Course Name	Credits	Retaken Credits	Desired Output for Column D
2	PS 101	Psychology	3	(Formula??)
3	EN 101	Engish	4	(Formula??)
4	SC 101	Science	3	(Formula??)
5	MA 101	Math	4	(Formula??)
6	MA 101	Math	4	(Formula??)	4
7	LA 101	Language	3	(Formula??)
8	LA 101	Language	3	(Formula??)	3
9	LA 101	Language	3	(Formula??)	3
10
11				Total Taken Credits	27
12				Total Retaken Credits	10
Sheet1

 

[Alex Blakenburg]

See if this works for you.

20210503 Omit first occurence value.xlsx
	A	B	C	D	E
1	Course	Course Name	Credits	Retaken Credits	Desired Output for Column D
2	PS 101	Psychology	3
3	EN 101	Engish	4
4	SC 101	Science	3
5	MA 101	Math	4
6	MA 101	Math	4	4	4
7	LA 101	Language	3
8	LA 101	Language	3	3	3
9	LA 101	Language	3	3	3
10
11				Total Taken Credits	27
12				Total Retaken Credits	10
Data Table

Cell Formulas
Range	Formula
D2:D9	D2	=IF(ROW([@Course])=MATCH([@Course],[Course],0)+ROW(tblCourses[[#Headers],[Course]]),"",[@Credits])

 

[mrscottjr]

Fantastic! Thank you so much - worked like a charm!

 

[Alex Blakenburg]

Glad I could help.
I later thought I should have offered this as an option as well.
It relies on the $A$2:$A2 expanding each time it gets copied down a row.

=IF(COUNTIFS($A$2:$A2,[@Course])=1,"",[@Credits])