Home

Counta function

%3CLINGO-SUB%20id%3D%22lingo-sub-792347%22%20slang%3D%22en-US%22%3ECounta%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-792347%22%20slang%3D%22en-US%22%3E%3CP%3EI%20need%20some%20guidance%20on%20a%20Counta%20or%20countif%20function%20for%20the%20attached%20file.%3C%2FP%3E%3CP%3EI%20need%20a%20formula%20for%20cells%20D111%20and%20D112.%3C%2FP%3E%3CP%3EIt%20is%20a%20presence%2Fabsence%20table%2C%20with%20a%20star%20in%20column%20D%20(D2%3AD106)%20representing%20the%20presence%20of%20a%20species%20in%20that%20vegetation%20group%20(cell%20D1).%3C%2FP%3E%3CP%3EMore%20explanation%20in%20the%20spreadsheet%20itself.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-792347%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-792474%22%20slang%3D%22en-US%22%3ERe%3A%20Counta%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-792474%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F388347%22%20target%3D%22_blank%22%3E%40Eren_Reid%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20can%20use%20Pivot%20table%20to%20count%20values%20without%20repeats.%20File%20is%20attached%20for%20reference.%3C%2FP%3E%3CP%3EI%20think%20unique%20values%20without%20repeat%20in%26nbsp%3B%3CFONT%20color%3D%22%230000FF%22%3E%3CSTRONG%3EGenus%3C%2FSTRONG%3E%3C%2FFONT%3Eare%2036%20not%2032.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3CP%3ETauqeer%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-792491%22%20slang%3D%22en-US%22%3ERe%3A%20Counta%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-792491%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F239215%22%20target%3D%22_blank%22%3E%40tauqeeracma%3C%2FA%3E%26nbsp%3BThank%20you%20for%20this%3C%2FP%3E%3CP%3EI%20am%20aware%20of%20the%20pivot%20table%20but%20it%20is%20a%20lot%20of%20work%20especially%20when%20i%20might%20have%2020%20something%20veg%20groups%2C%20hence%20i%20was%20wondering%20of%20there%20is%20a%20countif%20unique%20formula%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-793558%22%20slang%3D%22en-US%22%3ERe%3A%20Counta%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-793558%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F388347%22%20target%3D%22_blank%22%3E%40Eren_Reid%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20that%20case%2C%20you%20can%20use%20supporting%20columns%20to%20count%20required%20values%20without%20repeats.%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20simply%20need%20to%20add%20new%20veg%20group%20in%20%3CSTRONG%3Esheet1%3C%2FSTRONG%3Eand%20drag%20formula%20in%20%3CSTRONG%3Esheet2%3C%2FSTRONG%3E.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERevised%20excel%20file%20attached.%3C%2FP%3E%3CP%3EHope%20it%20will%20help%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETauqeer%3C%2FP%3E%3C%2FLINGO-BODY%3E
Eren_Reid
New Contributor

I need some guidance on a Counta or countif function for the attached file.

I need a formula for cells D111 and D112.

It is a presence/absence table, with a star in column D (D2:D106) representing the presence of a species in that vegetation group (cell D1).

More explanation in the spreadsheet itself.

3 Replies

Hi @Eren_Reid 

 

You can use Pivot table to count values without repeats. File is attached for reference.

I think unique values without repeat in Genus are 36 not 32.

 

Thanks

Tauqeer

 

@tauqeeracma Thank you for this

I am aware of the pivot table but it is a lot of work especially when i might have 20 something veg groups, hence i was wondering of there is a countif unique formula?

Highlighted

@Eren_Reid 

 

In that case, you can use supporting columns to count required values without repeats. 

You simply need to add new veg group in sheet1 and drag formula in sheet2.

 

Revised excel file attached.

Hope it will help

 

Tauqeer

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