Home

COUNTIFS distinct

%3CLINGO-SUB%20id%3D%22lingo-sub-167093%22%20slang%3D%22en-US%22%3ECOUNTIFS%20distinct%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-167093%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Eplease%20help%20me%20find%20a%20solution%3A%3C%2FP%3E%0A%3CP%3EI%20need%20to%20count%20the%20number%20of%20distinct%20text%20and%20number%20values%20in%20column%20%24M%3A%24M%20%2C%20when%20in%20column%20%24E%3A%24E%20is%20written%20%22*italija*%22%2C%20and%20when%20in%20column%20%24F%3A%24F%20is%20written%20%22saus%22%2C%20also%20-%20it%20should%20not%20count%20blank%20cells.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ENow%20I%20am%20doing%20this%20in%20pivot%3A%20filtering%20column%20F%20with%20%22saus%22%2C%20than%20filtering%20column%20E%20with%20%22italija%22%2C%20than%20copying%20column%20M%20in%20another%20sheet%2C%20removing%20dublicates%20and%20counting%20the%20cells.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-167093%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-167316%22%20slang%3D%22en-US%22%3ERe%3A%20COUNTIFS%20distinct%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-167316%22%20slang%3D%22en-US%22%3E%3CP%3EKarolis%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22http%3A%2F%2Fwww.contextures.com%2Fpivottablecountunique.html%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3ECount%20Unique%20Items%20in%20Pivot%20Table%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-799425%22%20slang%3D%22en-US%22%3ERe%3A%20COUNTIFS%20distinct%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-799425%22%20slang%3D%22en-US%22%3E%3CP%3E%3Dcountifs(E%3AE%2C%22*%22%26amp%3B%22Italija%22%26amp%3B%22*%22%2CF%3AF%2C%22saus%22)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-799519%22%20slang%3D%22en-US%22%3ERe%3A%20COUNTIFS%20distinct%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-799519%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F106537%22%20target%3D%22_blank%22%3E%40Karolis%20D%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAnd%20here%20how%20to%20do%20that%20with%20formulas%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fexceljet.net%2Fformula%2Fcount-unique-values-in-a-range-with-countif%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3ECount%20unique%20values%20in%20a%20range%20with%20COUNTIF%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Karolis D
Occasional Contributor

Hello, 

 

please help me find a solution:

I need to count the number of distinct text and number values in column $M:$M , when in column $E:$E is written "*italija*", and when in column $F:$F is written "saus", also - it should not count blank cells.

 

Now I am doing this in pivot: filtering column F with "saus", than filtering column E with "italija", than copying column M in another sheet, removing dublicates and counting the cells.

3 Replies

=countifs(E:E,"*"&"Italija"&"*",F:F,"saus")

@Karolis D 

And here how to do that with formulas

Count unique values in a range with COUNTIF

 

Related Conversations
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Early preview of Microsoft Edge group policies
Sean Lyndersay in Discussions on
65 Replies
*Updated 9/3* Syncing in Microsoft Edge Preview Channels
Elliot Kirk in Articles on
201 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
2 Replies