Home

Count distinct text values with multiple criteria

%3CLINGO-SUB%20id%3D%22lingo-sub-1024545%22%20slang%3D%22en-US%22%3ECount%20distinct%20text%20values%20with%20multiple%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1024545%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3EI%20have%20a%20data%20set%20that%20i%20wish%20the%20count%20the%20number%20of%20distinct%20text%20values%20in%20a%20column%20given%20certain%20criteria%20from%20another%20column.%3C%2FP%3E%3CP%3EPlease%20see%20attached%20%22Example%20data%20Set%22.%26nbsp%3B%20I%20would%20like%20the%20count%20of%20distinct%20text%20values%20from%20the%20column%20%22name%22%20given%20that%20the%20Value%20in%20Column%20%22rank%22%20is%20C1.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20You!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1024545%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20for%20web%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1024595%22%20slang%3D%22en-US%22%3ERe%3A%20Count%20distinct%20text%20values%20with%20multiple%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1024595%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F464724%22%20target%3D%22_blank%22%3E%40rickyboshe%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAs%20variant%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DSUMPRODUCT((%24B%242%3A%24B%2413%3D%22C1%22)%2FCOUNTIFS(%24A%242%3A%24A%2413%2C%24A%242%3A%24A%2413%26amp%3B%22%22%2C%24B%242%3A%24B%2413%2C%24B%242%3A%24B%2413%26amp%3B%22%22))%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1032639%22%20slang%3D%22en-US%22%3EMicrosoft%20Excell%3A%20calculate%201st%20Sun%2C%202nd%20Sun%2C%203rd%20Sun%2C%204th%20Sun%2C%20and%205th%20Sun%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1032639%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20using%20Microsoft%20Excell%20on%20a%20Mac%20computer.%20I%20have%20created%20the%20attached%204%20Week%20Microsoft%20Excell%20Spreadsheet%20Calendar%20Template%2C%20which%20includes%20calendar%20dates.%20I%20use%20the%20calendar%20repetitively.%20I%20am%20looking%20for%20a%20way%20to%20place%20in%20cell%20C1%20the%20date%20for%20any%20particular%20Sunday%2C%20and%20calculate%201st%20Sun%2C%202nd%20Sun%2C%203rd%20Sun%2C%204th%20Sun%2C%20and%205th%20Sun%20of%20the%20month%2C%20in%20cells%20C11%2C%20C23%2C%20C35%2C%20and%20C47%20respectively.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20in%20advance.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1034488%22%20slang%3D%22en-US%22%3ERe%3A%20Microsoft%20Excell%3A%20calculate%201st%20Sun%2C%202nd%20Sun%2C%203rd%20Sun%2C%204th%20Sun%2C%20and%205th%20Sun%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1034488%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F464676%22%20target%3D%22_blank%22%3E%40Richard_James%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ERichard%2C%20I%20guess%20I've%20seen%20that%20as%20a%20separate%20question.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Visitor

Hello,

I have a data set that i wish the count the number of distinct text values in a column given certain criteria from another column.

Please see attached "Example data Set".  I would like the count of distinct text values from the column "name" given that the Value in Column "rank" is C1.

 

Thank You!

3 Replies
Highlighted

@rickyboshe 

As variant

=SUMPRODUCT(($B$2:$B$13="C1")/COUNTIFS($A$2:$A$13,$A$2:$A$13&"",$B$2:$B$13,$B$2:$B$13&""))
Highlighted

I am using Microsoft Excell on a Mac computer. I have created the attached 4 Week Microsoft Excell Spreadsheet Calendar Template, which includes calendar dates. I use the calendar repetitively. I am looking for a way to place in cell C1 the date for any particular Sunday, and calculate 1st Sun, 2nd Sun, 3rd Sun, 4th Sun, and 5th Sun of the month, in cells C11, C23, C35, and C47 respectively.

 

Thank you in advance.

 

Highlighted

@Richard_James 

Richard, I guess I've seen that as a separate question.