# 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
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 1 Startup Arabic Yes mickeymouse@aol.com Turkey Male Syrian Session 2 Startup Arabic Yes mickeymouse@aol.com Turkey Male Syrian Session 4 Startup Arabic Yes mickeymouse@aol.com Turkey Male Syrian Session 1 Startup English Yes mickeymouse@aol.com Turkey Male Syrian Session 2 Startup English Yes mickeymouse@aol.com Turkey Male Syrian Session 3 Startup English Yes mickeymouse@aol.com Turkey Male Syrian Session 5 SME Arabic Yes mickeymouse@aol.com Turkey Male Syrian

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
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
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
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.

Replies
11
Views
139
Replies
9
Views
247
Replies
8
Views
211
Replies
1
Views
283
Replies
10
Views
664

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.

### Which adblocker are you using?

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

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