COUNTIFS formula that removes duplicate values in array

[HeatherF]

Hello,

I have a spreadsheet with unique student IDs in column A. Each ID may have multiple rows with different values in column D (student activities--maybe one row is Baseball and one is Glee Club). The formula below counts all the other rows that match that row's column D (activity) and column H (class year).

Column O =COUNTIFS($D$1:$D$68861,D2,$H$1:$H$68861,H2,$A$1:$A$68861,"<>"&A2)

I want the next column to total up all the people each ID matched with across multiple activities. I was doing this: =SUMIF($A$1:$A$68861,A2,$O$1:$O$68861) but then I realized if that individual was in baseball and glee club with the same classmate, that classmate is counted twice.

Is there a way to run the COUNTIFS formula above, then go through and remove duplicate values from column A?

Any ideas are very appreciated!

Thanks,

Heather

 

[Ottsel]

It's hard to throw together an example without seeing something for me, but maybe combining the Frequency formula?

Count unique values among duplicates

How to count the number of unique values in a range that contains duplicate values in Excel.

support.microsoft.com

 

[HeatherF]

Thanks, I've pasted some of the table below. I was afraid it would be confusing. Basically O2 is counting all the people in Richard's class that also did Men's Swim and Dive. I want column P to count all the people from Richard's class that he did any activity with. But if I just SUM column O, it will count Martin as 3 people, because they did Men's Swim and Dive, Choir/Choral Club, and Delta Psi together.

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	ID_NUMBER	CATEGORY	CODE	ACTIVITY	STOP_DT	START_DT	NAME	CLASS	GENDER	RATING	MIN CONF CAPACITY	LTG	NETWORK MIN CONF CAPACITY - SINGLE ACTIVITY	NETWORK LTG - SINGLE ACTIVITY	ACTIVITY NETWORK SIZE
2	6454254	SPORTS	87	Men's Swim and Dive			RICHARD	1964	M	U	0	$375	$50,000	$49,910	1
3	6454254	STU_ACTS	4	Choir/Choral Club			RICHARD	1964	M	U	0	$375	$50,000	$49,910	1
4	6454254	STU_ACTS	125	Civil Rights Committee			RICHARD	1964	M	U	0	$375	$0	$0	0
5	6454254	STU_ACTS	274	Delta Psi			RICHARD	1964	M	U	0	$375	$50,000	$49,910	1
6	6454254	STU_ACTS	7	Gargoyle Society			RICHARD	1964	M	U	0	$375	$0	$0	0
7	6454254	STU_ACTS	102	Glee Club			RICHARD	1964	M	U	0	$375	$0	$0	0
8	6454254	STU_ACTS	9	Junior Advisors			RICHARD	1964	M	U	0	$375	$50,000	$49,910	1
9	6492235	SPORTS	87	Men's Swim and Dive			MARTIN	1964	M	10	50000	$49,910	$0	$375	1
10	6492235	STU_ACTS	1	Band/Symphony			MARTIN	1964	M	10	50000	$49,910	$0	$0	0
11	6492235	STU_ACTS	274	Delta Psi			MARTIN	1964	M	10	50000	$49,910	$0	$375	1
12	6492235	STU_ACTS	8	Choir/Choral Club			MARTIN	1964	M	10	50000	$49,910	$0	$375	1
13	6492235	STU_ACTS	9	Junior Advisors			MARTIN	1964	M	10	50000	$49,910	$0	$375	1
14	6492235	STU_ACTS	59	Marching Band			MARTIN	1964	M	10	50000	$49,910	$0	$0	0
15	6492235	STU_ACTS	56	Theater Production Participant			MARTIN	1964	M	10	50000	$49,910	$0	$0	0
16	6374307	SPORTS	93	Men's Ice Hockey			THOMAS	1963	M	U	0	$3,140	$0	$0	0
17	6374307	STU_ACTS	134	Gurgle			THOMAS	1963	M	U	0	$3,140	$0	$0	0
18	6374307	STU_ACTS	274	Kappa Alpha			THOMAS	1963	M	U	0	$3,140	$0	$0	0
19	6455382	SPORTS	99	Baseball			DAVID	1964	M	N	0	$1,060	$0	$0	0
Sheet1

Cell Formulas
Range	Formula
M2:M19	M2	=SUMIFS($K$1:$K$68861,$D$1:$D$68861,D2,$H$1:$H$68861,H2,$A$1:$A$68861,"<>"&A2)
N2:N19	N2	=SUMIFS($L$1:$L$68861,$D$1:$D$68861,D2,$H$1:$H$68861,H2,$A$1:$A$68861,"<>"&A2)
O2:O19	O2	=COUNTIFS($D$1:$D$68861,D2,$H$1:$H$68861,H2,$A$1:$A$68861,"<>"&A2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
M1:M19,N1	Other Type	Color scale		NO

 

[Eric W]

Tricky. Try:

Book1
	A			D			G	H							O	P
1	ID			Activity			Name	Class							Activity Network Size	Total Network Size
2	1			Men's Swim and Dive			Richard	1964							1	5
3	1			Choir/Choral Club			Richard	1964							1
4	1			Civil Rights Committee			Richard	1964							2
5	1			Delta Psi			Richard	1964							2
6	1			Gargoyle Society			Richard	1964							1
7	5			Choir/Choral Club			Mary	1964							1	5
8	5			Civil Rights Committee			Mary	1964							2
9	5			Chess club			Mary	1964							4
10	5			Gurgle			Mary	1964							0
11	3			Chess club			Ed	1964							4	5
12	3			Men's Swim and Dive			Ed	1964							1
13	3			Junior Advisors			Ed	1964							2
14	3			Men's Ice Hocky			Ed	1964							0
15	9			Glee club			Susan	1964							1	4
16	9			Chess club			Susan	1964							4
17	22			Civil Rights Committee			Bob	1964							2	5
18	22			Chess club			Bob	1964							4
19	22			Junior Advisors			Bob	1964							2
20	10			Chess club			Magnus	1964							4	6
21	10			Delta Psi			Magnus	1964							2
22	10			Glee club			Magnus	1964							1
23	10			Junior Advisors			Magnus	1964							2
24	99			Gargoyle Society			Elayne	1964							1	2
25	99			Delta Psi			Elayne	1964							2
26
Sheet11

Cell Formulas
Range	Formula
O2:O25	O2	=COUNTIFS($D$1:$D$68862,D2,$H$1:$H$68862,H2,$A$1:$A$68862,"<>"&A2)
P2:P25	P2	=IF(A2=A1,"",SUMPRODUCT(SIGN(FREQUENCY(IF(($H$2:$H$26=H2)*ISNUMBER(MATCH($D$2:$D$26,D2:INDEX(D:D,AGGREGATE(14,6,ROW(D2:D22)/(A2:A22=A2),1)),0)),$A$2:$A$26),$A$2:$A$26)))-1)

Adjust the ranges as needed. This will work for up to 20 activities per person. It would be easy to raise that number if necessary.

 

[HeatherF]

Tricky. Try:

Book1
	A			D			G	H							O	P
1	ID			Activity			Name	Class							Activity Network Size	Total Network Size
2	1			Men's Swim and Dive			Richard	1964							1	5
3	1			Choir/Choral Club			Richard	1964							1
4	1			Civil Rights Committee			Richard	1964							2
5	1			Delta Psi			Richard	1964							2
6	1			Gargoyle Society			Richard	1964							1
7	5			Choir/Choral Club			Mary	1964							1	5
8	5			Civil Rights Committee			Mary	1964							2
9	5			Chess club			Mary	1964							4
10	5			Gurgle			Mary	1964							0
11	3			Chess club			Ed	1964							4	5
12	3			Men's Swim and Dive			Ed	1964							1
13	3			Junior Advisors			Ed	1964							2
14	3			Men's Ice Hocky			Ed	1964							0
15	9			Glee club			Susan	1964							1	4
16	9			Chess club			Susan	1964							4
17	22			Civil Rights Committee			Bob	1964							2	5
18	22			Chess club			Bob	1964							4
19	22			Junior Advisors			Bob	1964							2
20	10			Chess club			Magnus	1964							4	6
21	10			Delta Psi			Magnus	1964							2
22	10			Glee club			Magnus	1964							1
23	10			Junior Advisors			Magnus	1964							2
24	99			Gargoyle Society			Elayne	1964							1	2
25	99			Delta Psi			Elayne	1964							2
26
Sheet11

Cell Formulas
Range	Formula
O2:O25	O2	=COUNTIFS($D$1:$D$68862,D2,$H$1:$H$68862,H2,$A$1:$A$68862,"<>"&A2)
P2:P25	P2	=IF(A2=A1,"",SUMPRODUCT(SIGN(FREQUENCY(IF(($H$2:$H$26=H2)*ISNUMBER(MATCH($D$2:$D$26,D2:INDEX(D:D,AGGREGATE(14,6,ROW(D2:D22)/(A2:A22=A2),1)),0)),$A$2:$A$26),$A$2:$A$26)))-1)

Adjust the ranges as needed. This will work for up to 20 activities per person. It would be easy to raise that number if necessary.

Wow, thank you! That is elaborate but would probably work. I kept trying to think last night about how I might use hidden helper columns to compile network size totals then subtract dupes but I am at a loss. Thanks for your effort!

 

[Eric W]

Yes, it is elaborate, but I couldn't think of an easier way. I'll ponder a bit more. Newer versions of Excel have some new functions including FILTER and UNIQUE which would make this a snap.

Anyway, glad I could help, and thanks for the feedback!