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!

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

mart37

Well-known Member
Excel 365 have functions like AGGREGATE - FILTER - UNIQUE. Maybe you can use those functions.

peeblescd

New Member
Excel 365 have functions like AGGREGATE - FILTER - UNIQUE. Maybe you can use those functions.
Thank you, though I'm looking more for an example of a formula that might work. I've read about FILTER and UNIQUE and don't understand how to write the function for this particular case.

jasonb75

Well-known Member
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"
Are duplicate rows possible? e.g. "Session 1" appearing twice for a single email address, with all of the other columns meeting the specified criteria?

Are there any other possible entries in column A that would not meet the criteria of Session 1 to 5?

peeblescd

New Member

Are duplicate rows possible? e.g. "Session 1" appearing twice for a single email address, with all of the other columns meeting the specified criteria?

Are there any other possible entries in column A that would not meet the criteria of Session 1 to 5?
Yes, duplicate rows are possible, and there are no other possible entries in column A--only the 5 listed entries I mentioned exist there.

jasonb75

Well-known 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)))

Eric W

MrExcel MVP

Here's one way without using the newer functions:

Book2
ABCDEFGHI
2Session 1Startup ArabicYesmickeymouse@aol.comTurkeyMaleSyrian2
3Session 2Startup ArabicYesmickeymouse@aol.comTurkeyMaleSyrian
4Session 4Startup ArabicYesmickeymouse@aol.comTurkeyMaleSyrian
5Session 1Startup EnglishYesmickeymouse@aol.comTurkeyMaleSyrian
6Session 2Startup EnglishYesmickeymouse@aol.comTurkeyMaleSyrian
7Session 3Startup EnglishYesmickeymouse@aol.comTurkeyMaleSyrian
8Session 5SME ArabicYesmickeymouse@aol.comTurkeyMaleSyrian
9Session 6Startup EnglishNoMinniemouse@aol.comGreeceFemaleSyrian
10Session 1Startup ArabicYesabc@aol.comTurkeyMaleSyrian
11Session 2Startup ArabicYesabc@aol.comTurkeyMaleSyrian
12Session 5Startup ArabicYesabc@aol.comTurkeyMaleSyrian
13
Sheet7
Cell Formulas
RangeFormula
I2I2=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),{1;1;1;1;1})>2)*(MATCH(D2:D12,D2:D12,0)=ROW(D2:D12)-ROW(D2)+1))

The formula is long, but not too terrible, but it is tricky.

I added some extra rows with additional test data, because I wasn't entirely sure what you were asking. In this list there are 2 emails that have all the criteria you want, and one that doesn't.

jasonb75

Well-known Member
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"
@Eric W, yours is still counting where there are less than 3 of the listed values found when there are duplicate rows. e.g. with the same session number in A2 and A3.

Eric W

MrExcel MVP
True enough.
@Eric W, yours is still counting where there are less than 3 of the listed values found when there are duplicate rows. e.g. with the same session number in A2 and A3.
True enough. I didn't see any duplicate rows in the sample data, so I figured I'd cross that bridge if it came up.

Incidentally, if I'm reading your formula correctly, you don't specifically look for "Session 1", "Session 2", etc. in your formula, you just look for 3 unique values. So if there's something like "Intro" or "Q&A Session" in column A, you'd get incorrect results.

jasonb75

Well-known Member
I didn't see any duplicate rows in the sample data, so I figured I'd cross that bridge if it came up.
So if there's something like "Intro" or "Q&A Session" in column A, you'd get incorrect results.
See posts 4 & 5.

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

1,129,941
Messages
5,639,103
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.

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