Need help with formula

[Queenie Lai]

Hello,

I am working on a formula to count the number of unique items in the 3rd column based on the criteria below:

Criteria

Sep

Elizabeth

The number of count is 3. The formula that works however, it's based on one criterion of the 2nd column: 

=SUM(IF(FREQUENCY(IF(C3:C13=F3,MATCH(D3:D13,D3:D13,0)),ROW(D3:D13)-ROW($C$3)+1),1))

The formula that does not work if I add one more criterion:

=SUM(IF(FREQUENCY(IF(AND(C3:C13=F3,B3:B13=E3),MATCH(D3:D13,D3:D13,0)),ROW(D3:D13)-ROW($C$3)+1),1))

Please help.

Sep	Elizabeth	Mary
Sep	Danny	Mary
Sep	Danny	Anna
Sep	Elizabeth	Jennifer
Sep	Elizabeth	Michelle
Sep	Elizabeth	Mary
Sep	Katy	Michelle
Oct	Elizabeth	Anna
Oct	Katy	Jennifer
Oct	Danny	Jennifer
Oct	Danny	Jennifer

[#2 · Accepted solution]

Hi Queenie,

You shall add one more IF

{=SUM(--(FREQUENCY(IF(B3:B13=E3,IF(C3:C13=F3,MATCH(D3:D13,D3:D13,0))),ROW(D3:D13)-ROW($C$3)+1)>0))}

The pattern is here https://exceljet.net/formula/count-unique-text-values-with-criteria

[#3]

Another array formula could be

{=SUM(IF(($C$3:$C$13=F3)*($B$3:$B$13=E3), 1/COUNTIFS($C$3:$C$13, F3, $D$3:$D$13, $D$3:$D$13, $B$3:$B$13, E3)), 0)}

[#4]

New here... I don't know how to post a new query. 

Requesting help. . .

Q: How to count the number of alphabets (example: F in this case) in a column with a formula ?

Gender

F

F

F

M

M

This is a smaller portion of 60,000 ROWS table. 

The answer should be 3 in this case. 

[#5]

Please use COUNTIF