• 412K Members
• 7,592 Online
• 468K Conversations
SOLVED

New Contributor

# Need help with formula

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

 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
4 Replies
Solution

# Re: Need help with formula

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

# Re: Need help with formula

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)}`

# Re: Need help with formula

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.

# Re: Need help with formula

Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Edge insider Dev bypasses IE mode website list
HotCakeX in Enterprise on
4 Replies