Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community
LIVE

Counting cells with a word within

Copper Contributor

Hello 

i have a column of 20 cells

i want to count the number of cells with the word "thyroïdie" within, even if thyroïdie is included in a longer word, or even if there are other words in the cell

how can i do ? thanks

6 Replies

@MarcBEDELE 

=COUNTIF(range,"*thyroïdie*")
Autres ATCD
Hypothyroidie, Menière, IVG
HTA
NON
HTA
Goujerot, hyper thyroïdie
Goujerot, HTA, dyslipidémie
Méningiome
Hypothyroïdie, Goujerot, HTO, chir baria
NON
FA, SAOS, HTA
FA, FV, HTA, VHA
Bouveret, UGD
Mal perfo plantaire, ESV
PNS, césarienne
NON
NON
asthme, CMV, toxidermie
IM, appendicite, pso au CIMZIA, fausse couche
Morton, zona
CREST, stéatose, SAOS, RGO, coqueluche, GCAO, synoviorthèse
Hyper thyroïdie, varicelle, HTA, bicuspidie, ESV, asthme, nodules, chondrocalcinose

This is my column
this is my formula
=NB.SI(X2:X22;"*thyroidie*")

it puts 0
I get a count of 1.
surprising... but it is still not the good answer ( i expect 4 )

@MarcBEDELE 

That is because "thyroidie" is not identical to "thyroïdie".

 

=SUM(COUNTIF(X2:X22,{"*thyroidie*","*thyroïdie*"}))

 

 

EDIT:

=COUNTIF(X2:X22,"*thyro*die*")