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.
 
It is a valid formula. I copied and pasted it directly. I changed the formula to what you've written and got the same result of 1,272
 
Upvote 0

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
It is a valid formula.
Hmm, here is what I get when I copy/paste from your post

1613785014068.png


and when I click OK to the message the cursor is positioned where the second IFERROR argument 'value_if_error' should be:

1613785198873.png
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

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
Back
Top