Formula to count unique values based on multiple criteria, including 1 column where at least 3 of 5 criteria should be found?

peeblescd

New Member
Joined
Feb 16, 2021
Messages
12
Office Version
  1. 365
Platform
  1. Windows
Hello everyone, I'm not sure what kind of function of is needed here, though here's a sample of the data and what I'm trying to do:

Session 1Startup ArabicYesmickeymouse@aol.comTurkeyMaleSyrian
Session 2Startup ArabicYesmickeymouse@aol.comTurkeyMaleSyrian
Session 4Startup ArabicYesmickeymouse@aol.comTurkeyMaleSyrian
Session 1Startup EnglishYesmickeymouse@aol.comTurkeyMaleSyrian
Session 2Startup EnglishYesmickeymouse@aol.comTurkeyMaleSyrian
Session 3Startup EnglishYesmickeymouse@aol.comTurkeyMaleSyrian
Session 5SME ArabicYesmickeymouse@aol.comTurkeyMaleSyrian

The formula should return a count of how many times an email address in column D meets these criteria:
  • At least 3 of the following 5 values are found for column A: "Session 1", "Session 2", "Session 3", "Session 4", and/or "Session 5"
  • Column B is "Startup Arabic"
  • Column C is "Yes"
  • Column D is "Turkey"
  • Column E is "Male"
  • Column F is "Syrian"
So according to the above data sample, the formula should return "1" since "mickeymouse@aol.com" met the desired criteria just once.

Using COUNTIFS would be ideal in this case if it weren't for column A; I can't figure out how to tell the formula to look for at least 3 of the 5 session numbers. Does anyone have ideas? Thank you in advance!
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,823
This takes care of the duplicate row issue:

=SUMPRODUCT((MMULT(--(COUNTIFS(A:A,"Session "&{1,2,3,4,5},B:B,"Startup Arabic",C:C,"Yes",E:E,"Turkey",F:F,"Male",G:G,"Syrian",D:D,D2:D12)>0),{1;1;1;1;1})>2)*(MATCH(D2:D12,D2:D12,0)=ROW(D2:D12)-ROW(D2)+1))
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

peeblescd

New Member
Joined
Feb 16, 2021
Messages
12
Office Version
  1. 365
Platform
  1. Windows
This appears to work correctly, but I have only done a very quick test.

If your version of excel is missing any of the functions used then this could prove difficult. Whilst not impossible, I would expect some extremely long formulas.
Book1
ABCDEFGHI
1Session 1Startup ArabicYesmickeymouse@aol.comTurkeyMaleSyrianStartup Arabic
2Session 2Startup ArabicYesmickeymouse@aol.comTurkeyMaleSyrianYes
3Session 4Startup ArabicYesmickeymouse@aol.comTurkeyMaleSyrianTurkey
4Session 1Startup EnglishYesmickeymouse@aol.comTurkeyMaleSyrianMale
5Session 2Startup EnglishYesmickeymouse@aol.comTurkeyMaleSyrianSyrian
6Session 3Startup EnglishYesmickeymouse@aol.comTurkeyMaleSyrian1
7Session 5SME ArabicYesmickeymouse@aol.comTurkeyMaleSyrian
Sheet2
Cell Formulas
RangeFormula
I6I6=LET(a,INDEX(UNIQUE(FILTER(A1:D7,(B1:B7=I1)*(C1:C7=I2)*(E1:E7=I3)*(F1:F7=I4)*(G1:G7=I5),"")),,4),c,SEQUENCE(1,ROWS(a)),SUM(IF(FREQUENCY(IF(MATCH(a,a,0)=c,c),c)>=3,1)))

Thank you very much for setting this up! I haven't seen a formula quite like this, so I have 2 questions for you. What do the letters "a" and "c" represent? And if I would like to search for text strings instead of using the index in this formula, could I do that? I believe this function would look something like this but with the index removed?:

=LET(a,INDEX(UNIQUE(FILTER(A1:D7,(B1:B7="Startup Arabic")*(C1:C7="Yes")*(E1:E7="Turkey")*(F1:F7="Male")*(G1:G7="Syrian"),"")),,4),c,SEQUENCE(1,ROWS(a)),SUM(IF(FREQUENCY(IF(MATCH(a,a,0)=c,c),c)>=3,1)))
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,644
Office Version
  1. 365
Platform
  1. Windows
What do the letters "a" and "c" represent?
Those are names applied to sections of the formula by using the LET function so that it is not necessary to repeat the same calculations multiple times.
'a' represents the section,
Excel Formula:
INDEX(UNIQUE(FILTER(A1:D7,(B1:B7="Startup Arabic")*(C1:C7="Yes")*(E1:E7="Turkey")*(F1:F7="Male")*(G1:G7="Syrian"),"")),,4)
which would be repeated 3 times if we didn't use LET. 'c' is used in a similar way.

if I would like to search for text strings instead of using the index in this formula
I'm not sure what you mean by that, INDEX is only used to isolate the email address column once the duplicates have been filtered out.

Have you looked at the alternative formula that @Eric W has put considerable effort into? That may be something that you're more familiar with.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,940
Messages
5,639,097
Members
417,072
Latest member
JaimeDee

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