Count Distinct across two columns

[lljones]

Hi - this the last question I have on a project, and it's a tricky one (to me!)

Description: I keeping a spreadsheet for monthly Reading Group that shows month/author(s)/title(s)/page count/author gender/etc.

[TABLE="width: 764"]
<colgroup><col><col span="2"><col><col span="2"><col><col></colgroup><tbody>[TR]
[TD]Month[/TD]
[TD]Author1[/TD]
[TD]Author2[/TD]
[TD]Title1[/TD]
[TD]Title2[/TD]
[TD]TitleCt[/TD]
[TD]BookPageCt[/TD]
[TD]AuthorGender[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]Steinbeck, John[/TD]
[TD] [/TD]
[TD]The Grapes of Wrath[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]423[/TD]
[TD]m[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]Atwood, Margaret[/TD]
[TD] [/TD]
[TD]Cat's Eye[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]423[/TD]
[TD]f[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]Gaiman, Neil[/TD]
[TD]Pratchett, Terry[/TD]
[TD]Good Omens[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]432[/TD]
[TD]m[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]Selvon, Sam[/TD]
[TD] [/TD]
[TD]The Lonely Londoners[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]432[/TD]
[TD]m[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD]Lively, Penelope[/TD]
[TD] [/TD]
[TD]Moon Tiger[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]432[/TD]
[TD]f[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD]Stephanie Meyer[/TD]
[TD]Bram Stoker[/TD]
[TD]Twilight[/TD]
[TD]Dracula[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]867[/TD]
[TD]f[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD]Austen, Jane[/TD]
[TD] [/TD]
[TD]Persuasion[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]423[/TD]
[TD]f[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD]Pratchett, Terry[/TD]
[TD] [/TD]
[TD]The Colour of Magic[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]423[/TD]
[TD]m[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD]Steinbeck, John[/TD]
[TD] [/TD]
[TD]Tortilla Flat[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]432[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD]Highsmith, Patricia[/TD]
[TD][/TD]
[TD="colspan: 2"]The Talented Mr. Ripley[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]432[/TD]
[TD]f[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]


[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
MonthCt = 10
TitleCt = sum(titlect) = 11
AuthorCt = 9 (male = 4, female = 5)

Problem 1: I want a count of distinct authors across TWO columns. In the sample data above, Steinbeck & Pratchett are repeated and two months have co-authors. Correct count of unique authors = 9.

Problem 2: Gender and other demographic data is kept for the first author only, but I don't want duplicates. I'm managing this by leaving demo data blank for the second or subsequent instance of the author. It's working for me, but is there a better way to do this?

Thanks for your help - you guys are great!

 

[Gerald Higgins]

Just an observation . . .

Correct count of unique authors = 9.

Looks like 10 to me, or am I missing something ?
[TABLE="width: 180"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Atwood, Margaret
[/TD]
[/TR]
[TR]
[TD]Austen, Jane
[/TD]
[/TR]
[TR]
[TD]Bram Stoker
[/TD]
[/TR]
[TR]
[TD]Gaiman, Neil[/TD]
[/TR]
[TR]
[TD]Highsmith, Patricia[/TD]
[/TR]
[TR]
[TD]Lively, Penelope
[/TD]
[/TR]
[TR]
[TD]Pratchett, Terry
[/TD]
[/TR]
[TR]
[TD]Selvon, Sam
[/TD]
[/TR]
[TR]
[TD]Steinbeck, John
[/TD]
[/TR]
[TR]
[TD]Stephanie Meyer
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Eric W]

I also came up with 10 authors using this formula:

=SUMPRODUCT((B2:C11<>"")/(COUNTIF(B2:C11,B2:C11&"")))

As far as your second question, you could add another column with the heading: Author2Gender. It might be quite tricky though to figure out the number of unique male authors and the number of unique female authors. If what you have works, you could stick with it though. But I'm sure someone here can figure something out if you want that.

 

[lljones]

Yes, sorry, I keep confusing myself

Overall count of author instances = 12
Unique author count = 10 <-- this is the count I'm trying to get

 

[lljones]

Yay! That worked perfectly for the unique authors. Is there a way to do it with B:B/C:C or named ranges, though.

As for the the second question, managing it with blanks works fine for me, but feels kludgy if I ever try to hand the spreadsheet off to someone else. So, if anyone has any ideas without spending too much time on it, I'd appreciate it.

 

[Eric W]

I wouldn't recommend full column references because of performance, but you can use a name range like so:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Month	Author1	Author2	Title1	Title2	TitleCt	BookPageCt	AuthorGender	Author2Gender		Number of unique authors	Number of unique male authors	Number of unique female authors
2	1	Steinbeck, John		The Grapes of Wrath		1	423	m			10	5	5
3	2	Atwood, Margaret		Cat's Eye		1	423	f
4	3	Gaiman, Neil	Pratchett, Terry	Good Omens		1	432	m	m
5	4	Selvon, Sam		The Lonely Londoners		1	432	m
6	5	Lively, Penelope		Moon Tiger		1	432	f
7	6	Stephanie Meyer	Bram Stoker	Twilight	Dracula	2	867	f	m
8	7	Austen, Jane		Persuasion		1	423	f
9	8	Pratchett, Terry		The Colour of Magic		1	423	m
10	9	Steinbeck, John		Tortilla Flat		1	432	m
11	10	Highsmith, Patricia		The Talented Mr. Ripley		1	432	f
Sheet5

Cell Formulas
Range	Formula
K2	=SUMPRODUCT((Authors<>"")/(COUNTIF(Authors,Authors&"")))
L2	=SUMPRODUCT((Authors<>"")*(Genders="m")/(COUNTIF(Authors,Authors&"")))
M2	=K2-L2

Named Ranges
Name	Refers To	Cells
Authors	=Sheet5!$B$2:INDEX(Sheet5!$C:$C,MATCH("zzzzz",Sheet5!$B:$B))
Genders	=Sheet5!$H$2:INDEX(Sheet5!$I:$I,MATCH("zzzzz",Sheet5!$B:$B))

If you add an Author2Gender column, the L2 formula will give you the number of unique male authors. You can do the same for female authors, but just subracting (M2) works just as well.

 

[lljones]

You can do the same for female authors, but just subracting (M2) works just as well.

Until our Reading Group takes on the first non-binary author, that is.

Thanks for all your help, Eric. I will save this thread for future updates, but I've got what I need to go live for now. (I'm building this spreadsheet in Excel then publishing it on Google Docs to share with the group. Haven't quite figured out how to do some things on Google, like named ranges, so I'll keep poking away.)

Really do appreciate your help.