How to get unique values from Countifs funcation

[petes]

Name (A Column)	Marks (B Column)
John	3
John	3
John	2

The above is my data and have used the following formula. The formula should give the result as "1" instead of "2". Because, John is having same marks in 2 rows. Let me know how to tweak this formula

John (E column)

=COUNTIFS(A1:A3,E1,B1:B3,">2")

 

[kweaver]

Who knows what's in E1? Please post a complete set of examples using XL2BB and change your profile to indicate what version of Excel you are using.

 

[petes]

Ok, Please see the sample file attached. The result in B8 should give me 1 instead of 2. Excel version is 16.75 (23070901)

sample.xlsx
	A	B
1	Name	Marks
2	John	3
3	John	3
4	John	2
5	Mark	5
6
7		Result
8	John	2
9	Mark	1
Sheet2

Cell Formulas
Range	Formula
B8:B9	B8	=COUNTIFS($A$2:$A$5,A8,$B$2:$B$5,">2")

 

[kweaver]

Your formula calculates that JOHN has 2 entries in column B that are larger than 2. What's incorrect about that? What would the result be 1?
Is version 16.75 a MAC or Windows version? Are you running Excel 365?

 

[kweaver]

If you're using 365, is this what you want?

Book2
	A	B	C	D	E	F
1	Name	Marks				Result
2	John	3			John	2
3	John	3			Mark	3
4	John	2
5	Mark	5
6	John	10
7	Mark	5
8	Mark	5
9	Mark	3
10	Mark	3
11	Mark	100
12	Mark	1
Sheet1

Cell Formulas
Range	Formula
F2:F3	F2	=COUNT(UNIQUE(FILTER($B:$B,($A:$A=E2)*($B:$B>2))))

 

[petes]

The reason I need 1 in the result because, John has similar entries in 2 rows and hence it should be considered as duplicate and the result should be 1.
Note: There could be more than 2 entries also, in all these case the result should produce 1 because there is only 1 unique entry for John.

The "Filter" function is not valid in my version. Here is the version again:
Microsoft Excel for Mac
Version 16.75 (23070901)
Microsoft 365 Subscription

Thank you

 

[kweaver]

Did you try my solution? That should work on 365.

 

[Peter_SSs]

Microsoft 365 Subscription

Please put that information (& platform) in your account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)
The information then shows up all the time at the left of your posts like this:

I agree with @kweaver that the FILTER function should be available in 365 for Mac as shown In Microsoft's Help:

 

[petes]

Sorry, this function works and looks like I got the resolution. Need to check few more things and then mark it as resolved.

Thank again!

 

[kweaver]

Great. Was it my solution that worked for you?