How to exclude duplicate rows when summing values in columns

kakehavata

New Member
Joined
Mar 29, 2021
Messages
9
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
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:
Excel Formula:
=SUMPRODUCT((($A2:$A15="B")*(M2:M15))/(COUNTIFS($B2:$B15,$B2:$B15,$A2:$A15,"=B")+($A2:$A15<>"B")))

and this array formula for M24:
Excel Formula:
=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
ABCDEFGHIKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQAR
1IN-CLASS WORKHOMEWORKIIIIIIIVVVIVIIVIII
2BLATIN 1.0545452335
3KPHILOSOPHY AND MUSIC11260602222223
4IPHILOSOPHY1129060222222
5IPHILOSOPHY1126060222222
6BMUSIC AESTHETICS16.781351354333333333
7EIT243060315152431
8EIT246012061515433
9BPEDAGOGY11260602222222
10KMUSIC PSYCHOLOGY1445453333331
11KPHILOSOPHY AND MUSIC11260602222222
12KPHILOSOPHY OF MUSIC1230302221
13IETHICS AND PEDAGOGY1245453333331
14BLATIN 11454533341
15BOLD MUSICAL TEXTS1245453333331
16
17
18
19SEMESTERIIIIIIIVVVIVIIVIII
20HOURS/WEEK FOR EACH
21
22B.7.55008.5333
23Е.00030000
24I05000000
25K.54060000
26
27
Sheet1
Cell Formulas
RangeFormula
H11,H7:H8,H2:H3H2=G2*C2
I7:I8I7=SUM(G7,H7)/30
V7V7=(G7/15)/AQ7
W7W7=(H7/15)/AQ7
X7,O13:O15,R11:R12,O9:O11X7=SUM(V7,W7)/2
M13:M15,M9:M11M9=(G9/15)/AQ9
N13:N15,N9:N11N9=(H9/15)/AQ9
P11:P12P11=(G11/15)/AQ11
Q11:Q12Q11=(H11/15)/AQ11
M22,AH22,AE22,AB22,Y22,V22,S22,P22M22=SUMPRODUCT((($A2:$A15="B")*(M2:M15))/(COUNTIFS($B2:$B15,$B2:$B15,$A2:$A15,"=B")+($A2:$A15<>"B")))
M23,AH23,AE23,AB23,Y23,V23,S23,P23M23=SUMPRODUCT((($A2:$A15="E")*(M2:M15))/(COUNTIFS($B2:$B15,$B2:$B15,$A2:$A15,"=E")+($A2:$A15<>"E")))
M24M24=A1
M25,AH25,AE25,AB25,Y25,V25,S25,P25M25=SUM(IFERROR(M2:M15/COUNTIFS($A2:$A15,"K",$B2:$B15,$B2:$B115,M2:M15,M2:M15),0))
P24,AH24,AE24,AB24,Y24,V24,S24P24=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
CellConditionCell FormatStop If True
A3:AK3Expression=$A3="B"textNO
A3:AN3Expression=$A3="O"textNO
A3:AN3Expression=$A3="I"textNO
A3:AN3Expression=$A3="K"textNO
A3:AN3Expression=$A3="Б"textNO
A3:AN3Expression=$A3="E"textNO
B2Expression=$A2="O"textNO
B2Expression=$A2="I"textNO
B2Expression=$A2="K"textNO
B2Expression=$A2="Б"textNO
B2Expression=$A2="E"textNO
A4:AK4Expression=$A4="O"textNO
A4:AK4Expression=$A4="I"textNO
A4:AK4Expression=$A4="K"textNO
A4:AK4Expression=$A4="Б"textNO
A4:AK4Expression=$A4="E"textNO
M1:AJ1Expression=$A1="B"textNO
A8:AK8Expression=$A8="O"textNO
A8:AK8Expression=$A8="I"textNO
A8:AK8Expression=$A8="K"textNO
A8:AK8Expression=$A8="Б"textNO
A8:AK8Expression=$A8="E"textNO
A7:AR7Expression=$A7="O"textNO
A7:AR7Expression=$A7="I"textNO
A7:AR7Expression=$A7="K"textNO
A7:AR7Expression=$A7="Б"textNO
A7:AR7Expression=$A7="E"textNO
A2:AK2Expression=$A2="E"textNO
A2:AK2Expression=$A2="O"textNO
A2:AK2Expression=$A2="I"textNO
A2:AK2Expression=$A2="K"textNO
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:AK6Expression=$A2="B"textNO
A29:AS31,AS1:AS3,A5:AS6,A9:AS15,AS7,AL8:AS8,A16,AS16:AS17Expression=$A1="O"textNO
A29:AS31,AS1:AS3,A5:AS6,A9:AS15,AS7,AL8:AS8,A16,AS16:AS17Expression=$A1="I"textNO
A29:AS31,AS1:AS3,A5:AS6,A9:AS15,AS7,AL8:AS8,A16,AS16:AS17Expression=$A1="K"textNO
A29:AS31,AS1:AS3,A5:AS6,A9:AS15,AS7,AL8:AS8,A16,AS16:AS17Expression=$A1="Б"textNO
A29:AS31,AS1:AS3,A5:AS6,A9:AS15,AS7,AL8:AS8,A16,AS16:AS17Expression=$A1="E"textNO


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

Thanks a lot!
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
567
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQ
1IN-CLASS WORKHOMEWORKIIIIIIIVVVIVIIVIIIIdentify 1st Occurrence
2BLATIN 15454523351
3KPHILOSOPHY AND MUSIC112606022222231
4IPHILOSOPHY11290602222221
5IPHILOSOPHY11260602222221
6BMUSIC AESTHETICS16.7813513543333333331
7EIT2430603151524311
8EIT2460120615154331
9BPEDAGOGY112606022222212
10KMUSIC PSYCHOLOGY14454533333311
11KPHILOSOPHY AND MUSIC112606022222222
12KPHILOSOPHY OF MUSIC12303022211
13IETHICS AND PEDAGOGY12454533333311
14BLATIN 114545333421
15BOLD MUSICAL TEXTS12454533333311
16
17
18
19SEMESTERIIIIIIIVVVIVIIVIII
20HOURS/WEEK FOR EACH Using Identify 1st Occurrence Column AK
21
22B.75008.5333
23Е.00030000
24I75000000
25K.54060000
26
27
Sheet1
Cell Formulas
RangeFormula
H2:H3,H11,H7:H8H2=G2*C2
I7:I8I7=SUM(G7,H7)/30
V7V7=(G7/15)/AP7
W7W7=(H7/15)/AP7
X7,O13:O15,R11:R12,O9:O11X7=SUM(V7,W7)/2
M9:M11,M13:M15M9=(G9/15)/AP9
N9:N11,N13:N15N9=(H9/15)/AP9
P11:P12P11=(G11/15)/AP11
Q11:Q12Q11=(H11/15)/AP11
AK2:AK15AK2=COUNTIFS($A$2:$A2,$A2,$B$2:$B2,$B2,$G$2:$G2,$G2,$H$2:$H2,$H2)
M22:M25M22=SUMIFS($M$2:$M$15,$A$2:$A$15,LEFT($B22,1),$AK$2:$AK$15,1)
P22,AH22,AE22,AB22,Y22,V22,S22P22=SUMPRODUCT((($A2:$A15="B")*(P2:P15))/(COUNTIFS($B2:$B15,$B2:$B15,$A2:$A15,"=B")+($A2:$A15<>"B")))
P23,AH23,AE23,AB23,Y23,V23,S23P23=SUMPRODUCT((($A2:$A15="E")*(P2:P15))/(COUNTIFS($B2:$B15,$B2:$B15,$A2:$A15,"=E")+($A2:$A15<>"E")))
P24,AH24,AE24,AB24,Y24,V24,S24P24=SUM(IFERROR(P2:P15/COUNTIFS($A2:$A15,"I",$B2:$B15,$B2:$B115,P2:P15,P2:P15),0))
P25,AH25,AE25,AB25,Y25,V25,S25P25=SUM(IFERROR(P2:P15/COUNTIFS($A2:$A15,"K",$B2:$B15,$B2:$B115,P2:P15,P2:P15),0))
 
Solution

kakehavata

New Member
Joined
Mar 29, 2021
Messages
9
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
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
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQ
1IN-CLASS WORKHOMEWORKIIIIIIIVVVIVIIVIIIIdentify 1st Occurrence
2BLATIN 15454523351
3KPHILOSOPHY AND MUSIC112606022222231
4IPHILOSOPHY11290602222221
5IPHILOSOPHY11260602222221
6BMUSIC AESTHETICS16.7813513543333333331
7EIT2430603151524311
8EIT2460120615154331
9BPEDAGOGY112606022222212
10KMUSIC PSYCHOLOGY14454533333311
11KPHILOSOPHY AND MUSIC112606022222222
12KPHILOSOPHY OF MUSIC12303022211
13IETHICS AND PEDAGOGY12454533333311
14BLATIN 114545333421
15BOLD MUSICAL TEXTS12454533333311
16
17
18
19SEMESTERIIIIIIIVVVIVIIVIII
20HOURS/WEEK FOR EACH Using Identify 1st Occurrence Column AK
21
22B.75008.5333
23Е.00030000
24I75000000
25K.54060000
26
27
Sheet1
Cell Formulas
RangeFormula
H2:H3,H11,H7:H8H2=G2*C2
I7:I8I7=SUM(G7,H7)/30
V7V7=(G7/15)/AP7
W7W7=(H7/15)/AP7
X7,O13:O15,R11:R12,O9:O11X7=SUM(V7,W7)/2
M9:M11,M13:M15M9=(G9/15)/AP9
N9:N11,N13:N15N9=(H9/15)/AP9
P11:P12P11=(G11/15)/AP11
Q11:Q12Q11=(H11/15)/AP11
AK2:AK15AK2=COUNTIFS($A$2:$A2,$A2,$B$2:$B2,$B2,$G$2:$G2,$G2,$H$2:$H2,$H2)
M22:M25M22=SUMIFS($M$2:$M$15,$A$2:$A$15,LEFT($B22,1),$AK$2:$AK$15,1)
P22,AH22,AE22,AB22,Y22,V22,S22P22=SUMPRODUCT((($A2:$A15="B")*(P2:P15))/(COUNTIFS($B2:$B15,$B2:$B15,$A2:$A15,"=B")+($A2:$A15<>"B")))
P23,AH23,AE23,AB23,Y23,V23,S23P23=SUMPRODUCT((($A2:$A15="E")*(P2:P15))/(COUNTIFS($B2:$B15,$B2:$B15,$A2:$A15,"=E")+($A2:$A15<>"E")))
P24,AH24,AE24,AB24,Y24,V24,S24P24=SUM(IFERROR(P2:P15/COUNTIFS($A2:$A15,"I",$B2:$B15,$B2:$B115,P2:P15,P2:P15),0))
P25,AH25,AE25,AB25,Y25,V25,S25P25=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

Well-known Member
Joined
Feb 23, 2021
Messages
567
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,489
Messages
5,636,626
Members
416,932
Latest member
mm07

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top