How to exclude duplicate rows when summing values in columns

[kakehavata]

Hi guys. I opened a thread yesterday about the same problem, but I couldn't fully integrate the solutions people gave me in my own project.

The chart below is a part of a course programme that has these elements:
1. In column A are the initials of four teachers.
2. Column B are the subjects.
3. Column G and H are estimated hours of work-in-class and homework.
4. On row 1 roman numericals in some columns (M, P, S, V...) represent semesters.
5. In each of these columns (M, P, S, V...) there are numerals which represent hours/week of work-in-class for the given subject.
6. Below (rows 22-25) represent the sum total of hours/week for each teacher per semester.

Thus, for example, teacher B has to teach 5 hours/week in class during the students' semester II.

Other values are irrelevant to the current task. Also, I've left out some other formulas and placed some random values to make my point.

The issue: In order to find the sum total of hours/week for each teacher per semester, one has to exclude the duplicate intances of teacher+subject+in-class work+homework. For example, row 2 and row 14 are dublicates in this respect and should be counted only once. This should take account of instances such as row 4 and row 5, which have the same teacher+subject+homework values, but not the same class-work value. What formula should I use for M22-25, P22-25, etc.?

In the chart (see column M), I have left some examples of previous solutions that were given to me, which, however, took into account only the teacher+subject in order for something to count as a duplicate. Thus, someone had advised me to use this formula for M22:

=SUMPRODUCT((($A2:$A15="B")*(M2:M15))/(COUNTIFS($B2:$B15,$B2:$B15,$A2:$A15,"=B")+($A2:$A15<>"B")))

and this array formula for M24:

=SUM(IFERROR(M2:M15/COUNTIFS($A2:$A15,"I",$B2:$B15,$B2:$B115,M2:M15,M2:M15),0))

However, they do not take into account the in-class work and homework values, and also sometimes fail even in terms of the teacher+subject values, as you can see in M22 (it says 7.5 but should in fact be 7) and in other cells.

Here's the chart:

ВСИЧКИ (Autosaved).xlsx
	A	B	C	D	E	F	G	H	I		K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR
1							IN-CLASS WORK	HOMEWORK					I			II			III			IV			V			VI			VII			VIII
2	B	LATIN	1.0			5	45	45					2	3	3										5
3	K	PHILOSOPHY AND MUSIC	1		1	2	60	60					2	2	2	2	2	2				3
4	I	PHILOSOPHY	1		1	2	90	60					2	2	2	2	2	2
5	I	PHILOSOPHY	1		1	2	60	60					2	2	2	2	2	2
6	B	MUSIC AESTHETICS	1		6.7	8	135	135																	4			3	3	3	3	3	3	3	3	3
7	E	IT	2			4	30	60	3		15	15										2	4	3																			1
8	E	IT	2			4	60	120	6		15	15										4	3	3
9	B	PEDAGOGY	1		1	2	60	60					2	2	2	2	2	2																									2
10	K	MUSIC PSYCHOLOGY	1			4	45	45					3	3	3							3	3	3																			1
11	K	PHILOSOPHY AND MUSIC	1		1	2	60	60					2	2	2	2	2	2																									2
12	K	PHILOSOPHY OF MUSIC	1			2	30	30								2	2	2																									1
13	I	ETHICS AND PEDAGOGY	1			2	45	45					3	3	3	3	3	3																									1
14	B	LATIN	1			1	45	45					3	3	3										4																		1
15	B	OLD MUSICAL TEXTS	1			2	45	45					3	3	3	3	3	3																									1
16
17
18
19								SEMESTER					I			II			III			IV			V			VI			VII			VIII
20								HOURS/WEEK FOR EACH
21
22		B.											7.5			5			0			0			8.5			3			3			3
23		Е.											0			0			0			3			0			0			0			0
24		I											0			5			0			0			0			0			0			0
25		K.											5			4			0			6			0			0			0			0
26
27
Sheet1

Cell Formulas
Range	Formula
H11,H7:H8,H2:H3	H2	=G2*C2
I7:I8	I7	=SUM(G7,H7)/30
V7	V7	=(G7/15)/AQ7
W7	W7	=(H7/15)/AQ7
X7,O13:O15,R11:R12,O9:O11	X7	=SUM(V7,W7)/2
M13:M15,M9:M11	M9	=(G9/15)/AQ9
N13:N15,N9:N11	N9	=(H9/15)/AQ9
P11:P12	P11	=(G11/15)/AQ11
Q11:Q12	Q11	=(H11/15)/AQ11
M22,AH22,AE22,AB22,Y22,V22,S22,P22	M22	=SUMPRODUCT((($A2:$A15="B")*(M2:M15))/(COUNTIFS($B2:$B15,$B2:$B15,$A2:$A15,"=B")+($A2:$A15<>"B")))
M23,AH23,AE23,AB23,Y23,V23,S23,P23	M23	=SUMPRODUCT((($A2:$A15="E")*(M2:M15))/(COUNTIFS($B2:$B15,$B2:$B15,$A2:$A15,"=E")+($A2:$A15<>"E")))
M24	M24	=A1
M25,AH25,AE25,AB25,Y25,V25,S25,P25	M25	=SUM(IFERROR(M2:M15/COUNTIFS($A2:$A15,"K",$B2:$B15,$B2:$B115,M2:M15,M2:M15),0))
P24,AH24,AE24,AB24,Y24,V24,S24	P24	=SUM(IFERROR(P2:P15/COUNTIFS($A2:$A15,"I",$B2:$B15,$B2:$B115,P2:P15,P2:P15),0))
Press CTRL+SHIFT+ENTER to enter array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A3:AK3	Expression	=$A3="B"	text	NO
A3:AN3	Expression	=$A3="O"	text	NO
A3:AN3	Expression	=$A3="I"	text	NO
A3:AN3	Expression	=$A3="K"	text	NO
A3:AN3	Expression	=$A3="Б"	text	NO
A3:AN3	Expression	=$A3="E"	text	NO
B2	Expression	=$A2="O"	text	NO
B2	Expression	=$A2="I"	text	NO
B2	Expression	=$A2="K"	text	NO
B2	Expression	=$A2="Б"	text	NO
B2	Expression	=$A2="E"	text	NO
A4:AK4	Expression	=$A4="O"	text	NO
A4:AK4	Expression	=$A4="I"	text	NO
A4:AK4	Expression	=$A4="K"	text	NO
A4:AK4	Expression	=$A4="Б"	text	NO
A4:AK4	Expression	=$A4="E"	text	NO
M1:AJ1	Expression	=$A1="B"	text	NO
A8:AK8	Expression	=$A8="O"	text	NO
A8:AK8	Expression	=$A8="I"	text	NO
A8:AK8	Expression	=$A8="K"	text	NO
A8:AK8	Expression	=$A8="Б"	text	NO
A8:AK8	Expression	=$A8="E"	text	NO
A7:AR7	Expression	=$A7="O"	text	NO
A7:AR7	Expression	=$A7="I"	text	NO
A7:AR7	Expression	=$A7="K"	text	NO
A7:AR7	Expression	=$A7="Б"	text	NO
A7:AR7	Expression	=$A7="E"	text	NO
A2:AK2	Expression	=$A2="E"	text	NO
A2:AK2	Expression	=$A2="O"	text	NO
A2:AK2	Expression	=$A2="I"	text	NO
A2:AK2	Expression	=$A2="K"	text	NO
A9:AK15,A16,N22:O22,Q22:R22,T22:U22,W22:X22,Z22:AA22,AC22:AD22,AF22:AG22,AI22:AK22,A21:G22,A19:H20,L22,L19:AK21,A2:AK2,A4:AK6	Expression	=$A2="B"	text	NO
A29:AS31,AS1:AS3,A5:AS6,A9:AS15,AS7,AL8:AS8,A16,AS16:AS17	Expression	=$A1="O"	text	NO
A29:AS31,AS1:AS3,A5:AS6,A9:AS15,AS7,AL8:AS8,A16,AS16:AS17	Expression	=$A1="I"	text	NO
A29:AS31,AS1:AS3,A5:AS6,A9:AS15,AS7,AL8:AS8,A16,AS16:AS17	Expression	=$A1="K"	text	NO
A29:AS31,AS1:AS3,A5:AS6,A9:AS15,AS7,AL8:AS8,A16,AS16:AS17	Expression	=$A1="Б"	text	NO
A29:AS31,AS1:AS3,A5:AS6,A9:AS15,AS7,AL8:AS8,A16,AS16:AS17	Expression	=$A1="E"	text	NO

I am struggling a lot, as I am completely new to this stuff but am requiered to work it out!!

Thanks a lot!

 

[Alex Blakenburg]

If you are prepared to use a "helper" column then this might be an option.
The helper column is column AK (Identify 1st Occurrence), note the criteria range starts out being 1 line (row 2:2) and expands as you copy it down (row 2:3 then 2:4) etc
I have then used Sumifs utilizing that column in your summary section in column M.

(I gather you are not on MS 365 which rules out using the new dynamic array functions.)

20210331 Sumifs Ignore Duplicates v02.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ
1							IN-CLASS WORK	HOMEWORK					I			II			III			IV			V			VI			VII			VIII			Identify 1st Occurrence
2	B	LATIN	1			5	45	45					2	3	3										5												1
3	K	PHILOSOPHY AND MUSIC	1		1	2	60	60					2	2	2	2	2	2				3															1
4	I	PHILOSOPHY	1		1	2	90	60					2	2	2	2	2	2																			1
5	I	PHILOSOPHY	1		1	2	60	60					2	2	2	2	2	2																			1
6	B	MUSIC AESTHETICS	1		6.7	8	135	135																	4			3	3	3	3	3	3	3	3	3	1
7	E	IT	2			4	30	60	3		15	15										2	4	3													1					1
8	E	IT	2			4	60	120	6		15	15										4	3	3													1
9	B	PEDAGOGY	1		1	2	60	60					2	2	2	2	2	2																			1					2
10	K	MUSIC PSYCHOLOGY	1			4	45	45					3	3	3							3	3	3													1					1
11	K	PHILOSOPHY AND MUSIC	1		1	2	60	60					2	2	2	2	2	2																			2					2
12	K	PHILOSOPHY OF MUSIC	1			2	30	30								2	2	2																			1					1
13	I	ETHICS AND PEDAGOGY	1			2	45	45					3	3	3	3	3	3																			1					1
14	B	LATIN	1			1	45	45					3	3	3										4												2					1
15	B	OLD MUSICAL TEXTS	1			2	45	45					3	3	3	3	3	3																			1					1
16
17
18
19								SEMESTER					I			II			III			IV			V			VI			VII			VIII
20								HOURS/WEEK FOR EACH					Using Identify 1st Occurrence Column AK
21
22		B.											7			5			0			0			8.5			3			3			3
23		Е.											0			0			0			3			0			0			0			0
24		I											7			5			0			0			0			0			0			0
25		K.											5			4			0			6			0			0			0			0
26
27
Sheet1

Cell Formulas
Range	Formula
H2:H3,H11,H7:H8	H2	=G2*C2
I7:I8	I7	=SUM(G7,H7)/30
V7	V7	=(G7/15)/AP7
W7	W7	=(H7/15)/AP7
X7,O13:O15,R11:R12,O9:O11	X7	=SUM(V7,W7)/2
M9:M11,M13:M15	M9	=(G9/15)/AP9
N9:N11,N13:N15	N9	=(H9/15)/AP9
P11:P12	P11	=(G11/15)/AP11
Q11:Q12	Q11	=(H11/15)/AP11
AK2:AK15	AK2	=COUNTIFS($A$2:$A2,$A2,$B$2:$B2,$B2,$G$2:$G2,$G2,$H$2:$H2,$H2)
M22:M25	M22	=SUMIFS($M$2:$M$15,$A$2:$A$15,LEFT($B22,1),$AK$2:$AK$15,1)
P22,AH22,AE22,AB22,Y22,V22,S22	P22	=SUMPRODUCT((($A2:$A15="B")*(P2:P15))/(COUNTIFS($B2:$B15,$B2:$B15,$A2:$A15,"=B")+($A2:$A15<>"B")))
P23,AH23,AE23,AB23,Y23,V23,S23	P23	=SUMPRODUCT((($A2:$A15="E")*(P2:P15))/(COUNTIFS($B2:$B15,$B2:$B15,$A2:$A15,"=E")+($A2:$A15<>"E")))
P24,AH24,AE24,AB24,Y24,V24,S24	P24	=SUM(IFERROR(P2:P15/COUNTIFS($A2:$A15,"I",$B2:$B15,$B2:$B115,P2:P15,P2:P15),0))
P25,AH25,AE25,AB25,Y25,V25,S25	P25	=SUM(IFERROR(P2:P15/COUNTIFS($A2:$A15,"K",$B2:$B15,$B2:$B115,P2:P15,P2:P15),0))

 

[kakehavata]

If you are prepared to use a "helper" column then this might be an option.
The helper column is column AK (Identify 1st Occurrence), note the criteria range starts out being 1 line (row 2:2) and expands as you copy it down (row 2:3 then 2:4) etc
I have then used Sumifs utilizing that column in your summary section in column M.

(I gather you are not on MS 365 which rules out using the new dynamic array functions.)

20210331 Sumifs Ignore Duplicates v02.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ
1							IN-CLASS WORK	HOMEWORK					I			II			III			IV			V			VI			VII			VIII			Identify 1st Occurrence
2	B	LATIN	1			5	45	45					2	3	3										5												1
3	K	PHILOSOPHY AND MUSIC	1		1	2	60	60					2	2	2	2	2	2				3															1
4	I	PHILOSOPHY	1		1	2	90	60					2	2	2	2	2	2																			1
5	I	PHILOSOPHY	1		1	2	60	60					2	2	2	2	2	2																			1
6	B	MUSIC AESTHETICS	1		6.7	8	135	135																	4			3	3	3	3	3	3	3	3	3	1
7	E	IT	2			4	30	60	3		15	15										2	4	3													1					1
8	E	IT	2			4	60	120	6		15	15										4	3	3													1
9	B	PEDAGOGY	1		1	2	60	60					2	2	2	2	2	2																			1					2
10	K	MUSIC PSYCHOLOGY	1			4	45	45					3	3	3							3	3	3													1					1
11	K	PHILOSOPHY AND MUSIC	1		1	2	60	60					2	2	2	2	2	2																			2					2
12	K	PHILOSOPHY OF MUSIC	1			2	30	30								2	2	2																			1					1
13	I	ETHICS AND PEDAGOGY	1			2	45	45					3	3	3	3	3	3																			1					1
14	B	LATIN	1			1	45	45					3	3	3										4												2					1
15	B	OLD MUSICAL TEXTS	1			2	45	45					3	3	3	3	3	3																			1					1
16
17
18
19								SEMESTER					I			II			III			IV			V			VI			VII			VIII
20								HOURS/WEEK FOR EACH					Using Identify 1st Occurrence Column AK
21
22		B.											7			5			0			0			8.5			3			3			3
23		Е.											0			0			0			3			0			0			0			0
24		I											7			5			0			0			0			0			0			0
25		K.											5			4			0			6			0			0			0			0
26
27
Sheet1

Cell Formulas
Range	Formula
H2:H3,H11,H7:H8	H2	=G2*C2
I7:I8	I7	=SUM(G7,H7)/30
V7	V7	=(G7/15)/AP7
W7	W7	=(H7/15)/AP7
X7,O13:O15,R11:R12,O9:O11	X7	=SUM(V7,W7)/2
M9:M11,M13:M15	M9	=(G9/15)/AP9
N9:N11,N13:N15	N9	=(H9/15)/AP9
P11:P12	P11	=(G11/15)/AP11
Q11:Q12	Q11	=(H11/15)/AP11
AK2:AK15	AK2	=COUNTIFS($A$2:$A2,$A2,$B$2:$B2,$B2,$G$2:$G2,$G2,$H$2:$H2,$H2)
M22:M25	M22	=SUMIFS($M$2:$M$15,$A$2:$A$15,LEFT($B22,1),$AK$2:$AK$15,1)
P22,AH22,AE22,AB22,Y22,V22,S22	P22	=SUMPRODUCT((($A2:$A15="B")*(P2:P15))/(COUNTIFS($B2:$B15,$B2:$B15,$A2:$A15,"=B")+($A2:$A15<>"B")))
P23,AH23,AE23,AB23,Y23,V23,S23	P23	=SUMPRODUCT((($A2:$A15="E")*(P2:P15))/(COUNTIFS($B2:$B15,$B2:$B15,$A2:$A15,"=E")+($A2:$A15<>"E")))
P24,AH24,AE24,AB24,Y24,V24,S24	P24	=SUM(IFERROR(P2:P15/COUNTIFS($A2:$A15,"I",$B2:$B15,$B2:$B115,P2:P15,P2:P15),0))
P25,AH25,AE25,AB25,Y25,V25,S25	P25	=SUM(IFERROR(P2:P15/COUNTIFS($A2:$A15,"K",$B2:$B15,$B2:$B115,P2:P15,P2:P15),0))

Thank you very much!! It works like a charm.

 

[Alex Blakenburg]

Thanks for letting me know. Glad I could help.

Would you mind hitting the "mark as solution" checkbox to the right of the post. It helps other volunteers isolate which queries still need assistance.