SOLVED
Home

Need help with formula

%3CLINGO-SUB%20id%3D%22lingo-sub-118754%22%20slang%3D%22en-US%22%3ENeed%20help%20with%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-118754%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3EI%20am%20working%20on%20a%20formula%20to%20count%20the%20number%20of%20unique%20items%20in%20the%203rd%20column%20based%20on%20the%20criteria%20below%3A%3C%2FP%3E%3CTABLE%20border%3D%220%22%20cellspacing%3D%220%22%20cellpadding%3D%220%22%3E%3CTBODY%3E%3CTR%3E%3CTD%3ECriteria%3C%2FTD%3E%3CTD%3ESep%26nbsp%3B%26nbsp%3B%3C%2FTD%3E%3CTD%3EElizabeth%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20number%20of%20count%20is%203.%20The%20formula%20that%20works%20however%2C%20it's%20based%20on%20one%20criterion%20of%20the%202nd%20column%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%3DSUM(IF(FREQUENCY(%3CSTRONG%3EIF(C3%3AC13%3DF3%3C%2FSTRONG%3E%2CMATCH(D3%3AD13%2CD3%3AD13%2C0))%2CROW(D3%3AD13)-ROW(%24C%243)%2B1)%2C1))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20formula%20that%20does%20not%20work%20if%20I%20add%20one%20more%20criterion%3A%3C%2FP%3E%3CP%3E%3CSPAN%3E%3DSUM(IF(FREQUENCY(%3CSTRONG%3EIF(AND(C3%3AC13%3DF3%2CB3%3AB13%3DE3)%3C%2FSTRONG%3E%2CMATCH(D3%3AD13%2CD3%3AD13%2C0))%2CROW(D3%3AD13)-ROW(%24C%243)%2B1)%2C1))%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20help.%3C%2FP%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3ESep%3C%2FTD%3E%3CTD%3EElizabeth%3C%2FTD%3E%3CTD%3EMary%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ESep%3C%2FTD%3E%3CTD%3EDanny%3C%2FTD%3E%3CTD%3EMary%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ESep%3C%2FTD%3E%3CTD%3EDanny%3C%2FTD%3E%3CTD%3EAnna%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ESep%3C%2FTD%3E%3CTD%3EElizabeth%3C%2FTD%3E%3CTD%3EJennifer%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ESep%3C%2FTD%3E%3CTD%3EElizabeth%3C%2FTD%3E%3CTD%3EMichelle%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ESep%3C%2FTD%3E%3CTD%3EElizabeth%3C%2FTD%3E%3CTD%3EMary%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ESep%3C%2FTD%3E%3CTD%3EKaty%3C%2FTD%3E%3CTD%3EMichelle%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EOct%3C%2FTD%3E%3CTD%3EElizabeth%3C%2FTD%3E%3CTD%3EAnna%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EOct%3C%2FTD%3E%3CTD%3EKaty%3C%2FTD%3E%3CTD%3EJennifer%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EOct%3C%2FTD%3E%3CTD%3EDanny%3C%2FTD%3E%3CTD%3EJennifer%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EOct%3C%2FTD%3E%3CTD%3EDanny%3C%2FTD%3E%3CTD%3EJennifer%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-118754%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20%26amp%3B%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ENeed%20Help%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-327660%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20with%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-327660%22%20slang%3D%22en-US%22%3E%3CP%3EPlease%20use%20COUNTIF%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-327583%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20with%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-327583%22%20slang%3D%22en-US%22%3E%3CP%3ENew%20here...%20I%20don't%20know%20how%20to%20post%20a%20new%20query.%26nbsp%3B%3C%2FP%3E%3CP%3ERequesting%20help.%20.%20.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EQ%3A%20How%20to%20count%20the%20number%20of%20alphabets%20(example%3A%20F%20in%20this%20case)%20in%20a%20column%20with%20a%20formula%20%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20border%3D%220%22%20cellspacing%3D%220%22%20cellpadding%3D%220%22%3E%3CTBODY%3E%3CTR%3E%3CTD%3E%3CSTRONG%3EGender%3C%2FSTRONG%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EF%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EF%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EF%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EM%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EM%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20a%20smaller%20portion%20of%2060%2C000%20ROWS%20table.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20answer%20should%20be%203%20in%20this%20case.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-118838%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20with%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-118838%22%20slang%3D%22en-US%22%3E%3CP%3EAnother%20array%20formula%20could%20be%3C%2FP%3E%3CPRE%3E%7B%3DSUM(IF((%24C%243%3A%24C%2413%3DF3)*(%24B%243%3A%24B%2413%3DE3)%2C%201%2FCOUNTIFS(%24C%243%3A%24C%2413%2C%20F3%2C%20%24D%243%3A%24D%2413%2C%20%24D%243%3A%24D%2413%2C%20%24B%243%3A%24B%2413%2C%20E3))%2C%200)%7D%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-118837%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20with%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-118837%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Queenie%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20shall%20add%20one%20more%20IF%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%3E%7B%3DSUM(--(FREQUENCY(IF(B3%3AB13%3DE3%2CIF(C3%3AC13%3DF3%2CMATCH(D3%3AD13%2CD3%3AD13%2C0)))%2CROW(D3%3AD13)-ROW(%24C%243)%2B1)%26gt%3B0))%7D%3C%2FPRE%3E%3CP%3EThe%20pattern%20is%20here%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fexceljet.net%2Fformula%2Fcount-unique-text-values-with-criteria%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fexceljet.net%2Fformula%2Fcount-unique-text-values-with-criteria%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Queenie Lai
New Contributor

Hello,

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

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

SepElizabethMary
SepDannyMary
SepDannyAnna
SepElizabethJennifer
SepElizabethMichelle
SepElizabethMary
SepKatyMichelle
OctElizabethAnna
OctKatyJennifer
OctDannyJennifer
OctDannyJennifer
4 Replies
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

 

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

 

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. 

Please use COUNTIF

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