Unique filter function yielding the wrong total count?

[peeblescd]

Hi everyone, I'm using a function where the total count is a different result to what I get when manually filtering using the same criteria. This is a sample of the dataset:

Session 1	Startup Arabic	Yes				sun@gmail.com
Session 1	Startup Arabic	Yes				stars@gmail.com
Session 1	Startup Arabic	Yes				earth@gmail.com
Session 1	Startup Arabic	Yes				mars@gmail.com
Session 2	Startup Arabic	Yes				sun@gmail.com
Session 2	Startup Arabic	Yes				jupiter@gmail.com
Session 2	Startup Arabic	Yes				pluto@gmail.com
Session 1	Startup English	Yes				pluto@gmail.com
Session 2	Startup English	Yes				mercury@gmail.com

The goal is to count the unique number of individuals (using the email address as the unique identifier) who participated in either Startup Arabic OR Startup English. All individuals in this sample dataset have participated (denoted by "Yes" in column C). In the full dataset, there is the possibility of "No" in column C to denote those who didn't attend. The full dataset contains about 25,000 rows. When I use the below formula, I get a result of 1,272:

=IFERROR(ROWS(UNIQUE(FILTER('All Session Data'!$G:$G,IFERROR(FIND("Startup",'All Session Data'!$B:$B)>0,0)*('All Session Data'!$C:$C="Yes")),0)))

However, when I manually filter the data, I get a result of just 1,127. To filter, I first filter column B to include Startup Arabic and Startup English. Then, I filter column C to be "Yes", and finally, I remove the duplicates from the resulting list of email addresses.

I can't figure out why manually filtering versus using the above formula is yielding such a different result. If anyone could give some advice as to what might be wrong with the formula, I'd really appreciate it.

 

[Eric W]

Are there any other values in column B with Startup in them? Startup Spanish maybe?

 

[peeblescd]

Are there any other values in column B with Startup in them? Startup Spanish maybe?

There are 2 other values: SME Arabic and SME English

 

[tyija1995]

Do any of the "Startup" values have lower case "startup"? You won't see this by filter, but manual inspection.

 

[peeblescd]

Do any of the "Startup" values have lower case "startup"?

No, they don't. The only possible values in column B are: Startup Arabic, Startup English, SME Arabic, and SME English -- all with this exact capitalization

 

[tyija1995]

Could you also try this formula:

COUNTA(UNIQUE(FILTER(G:G,(C:C="yes")*((B:B="Startup Arabic")+(B:B="Startup English")))))

Which value does it agree with, 1127 or 1272?

 

[peeblescd]

Could you also try this formula:

COUNTA(UNIQUE(FILTER(G:G,(C:C="yes")*((B:B="Startup Arabic")+(B:B="Startup English")))))

Which value does it agree with, 1127 or 1272?

This formula yields 1,272

 

[tyija1995]

The only thing I can think of is the remove duplicate functionality with the filter.

With an example I tried:

Unfiltered:

Book1
	A	B
1	email	filter on y
2	123	y
3	234	y
4	234	n
5	345	y
6	456	y
Sheet1

Filtered by "y":

Book1
	A	B
1	email	filter on y
2	123	y
3	234	y
5	345	y
6	456	y
Sheet1

After using dedupe tool:

Book1
	A	B
1	email	filter on y
2	123	y
3	234	y
5	456	y
6		y
Sheet1

Which gives me 3 for when I highlight, but really it is 4, one of the values got pushed into a row with a "n" so does not show up. Is that 1127 count when you highlight the rows with the filters still active? try remove the filters and then highlight col G see what you get?

POST EDIT:

After removing the filter:

Book1
	A	B
1	email	filter on y
2	123	y
3	234	y
4	345	n
5	456	y
6		y
Sheet1

 

[peeblescd]

Hmm I think I get what you're saying but I'm not deduping within the same sheet, so I don't think this is the issue. After applying the filters for Startup and Yes, I simply copy and paste the entire column G into a new sheet, highlight the range there, and select remove duplicates

 

[Peter_SSs]

When I use the below formula, I get a result of 1,272:

=IFERROR(ROWS(UNIQUE(FILTER('All Session Data'!$G:$G,IFERROR(FIND("Startup",'All Session Data'!$B:$B)>0,0)*('All Session Data'!$C:$C="Yes")),0)))

Actually, when you use that formula, you wouldn't get anything as it is not a valid formula.
Did you mean this?
=IFERROR(ROWS(UNIQUE(FILTER('All Session Data'!$G:$G,IFERROR(FIND("Startup",'All Session Data'!$B:$B)>0,0)*('All Session Data'!$C:$C="Yes")),0)),0)

Are you able to make up a small set of sample data that is more representative of your varied data but still displays this problem and post it with XL2BB