Unique filter function yielding the wrong total count?

peeblescd

New Member
Joined
Feb 16, 2021
Messages
12
Office Version
  1. 365
Platform
  1. Windows
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 1Startup ArabicYessun@gmail.com
Session 1Startup ArabicYesstars@gmail.com
Session 1Startup ArabicYesearth@gmail.com
Session 1Startup ArabicYesmars@gmail.com
Session 2Startup ArabicYessun@gmail.com
Session 2Startup ArabicYesjupiter@gmail.com
Session 2Startup ArabicYespluto@gmail.com
Session 1Startup EnglishYespluto@gmail.com
Session 2Startup EnglishYesmercury@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.
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,770
Are there any other values in column B with Startup in them? Startup Spanish maybe?
 

tyija1995

Well-known Member
Joined
Feb 26, 2019
Messages
780
Office Version
  1. 365
Platform
  1. Windows
Do any of the "Startup" values have lower case "startup"? You won't see this by filter, but manual inspection.
 

peeblescd

New Member
Joined
Feb 16, 2021
Messages
12
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Feb 26, 2019
Messages
780
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Feb 16, 2021
Messages
12
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Feb 26, 2019
Messages
780
Office Version
  1. 365
Platform
  1. Windows
The only thing I can think of is the remove duplicate functionality with the filter.

With an example I tried:

Unfiltered:

Book1
AB
1emailfilter on y
2123y
3234y
4234n
5345y
6456y
Sheet1


Filtered by "y":

Book1
AB
1emailfilter on y
2123y
3234y
5345y
6456y
Sheet1


After using dedupe tool:

Book1
AB
1emailfilter on y
2123y
3234y
5456y
6y
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
AB
1emailfilter on y
2123y
3234y
4345n
5456y
6y
Sheet1
 

peeblescd

New Member
Joined
Feb 16, 2021
Messages
12
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,872
Messages
5,627,393
Members
416,245
Latest member
Xterminat

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