NB.SI et Plusieurs valeurs dans une même cellule Résolu/Fermé

Question

Bonjour, 

J'aimerais connaître le moyen de cumuler une valeur x dans une colonne dans laquelle une cellule peut contenir plus d'une valeur.

Exemple :  
B2 : 1
B3 : 1,5, 7
B4 : 2,5
B5 : 1

ETC.

Comment puis-je connaître le nombre de 1 dans la colonne B (par exemple)?

=NB.SI(J4:J501;"1") serait la formule pour compter les élément contenant uniquement 1, mais je veux inclure tout ce qui contient 1.




Merci.


Configuration: Windows XP / Internet Explorer 8.0

Happy79_2 · Answer

Bon, finalement je crois avoir trouver la réponse alors je mes ma formule pour le bénéfice des autres :

=NB.SI($J$4:$J$501;"*1*")+NB.SI($J$4:$J$501;"1")-NB.SI($J$4:$J$501;"*10*")

Le dernier sert à éliminer un élément qui contien 1, mais qui n'est pas un 1.


Toutefois, si vous avez quelque chose de plus efficace pour quelqu'un qui aurait plus de variable comme allant jusqu'à 100. Ne gênez-vous surtout pas!

Mike-31 · Answer

Bonsoir,

si j'ai bien compris, tu peux utiliser cette formule

=SOMMEPROD((NBCAR(J4:J501)-NBCAR(SUBSTITUE(J4:J501;1;""))))

Happy79_2 · Answer

C'est presque ça!

Toutefois, cette formule n'exclut pas les nombres comme 10, 11, 21, 100, etc. qui contiennent  1. 

Votre formule équivaut à =NB.SI($J$4:$J$501;"*1*")+NB.SI($J$4:$J$501;"1").


Y a t'il mieux?

Vaucluse · Answer

Bonjour

difficile de s'y retrouver dans votre et vos solutions

NB.SI(Champ;"*1*") ne fonctionne que si les valeurs sont textes et pas numériques, sauf erreur de ma part dans mes tests
et ensuite vous voulez compter les nombres qui débutent par 1 mais en éliminant 10,100 ect...
s'il s'agit donc de compter uniquement des nombres entre 1 et 2 en incluant 1, mieux vaut:

=NB.SI.ENS(J4:J504;">=1";J4:J540;"<2")

crdlmnt

michel_m · Answer

Bonjour
=SOMMEPROD((ENT(B2:B16)=1)*1)

Happy79_2 · Answer

Désolé si ma solutions impliste vous mélange.

Je recommence en modifiant mon exemple :

Exemple : 
j4 : 1
j5: 1,5, 7 
j6 : 2,5 
j7 : 10 
j8 : 2, 10

Je veux compter le nombre de valeur «1» tout en excluant les autres nombres contenant «1» comme 10 ou 11 ou 100.

J'ai copier votre formule =NB.SI.ENS(J4:J504;">=1";J4:J540;"<2"), mais elle me fait le code d'erreur #NOM? 
(et ce même si j'ai 540 au deux plages)

ccm81 · Answer

Bonjour à tous

Peut être via vba
	https://www.cjoint.com/?3Kyq0Kmc4FC 

Cdlmnt

Happy79_2 · Answer

Vous ne savez pas à quel point j'aimerais utiliser ce site, mais il est bloqué à mon travail...

ccm81 · Answer

Alt-F11 pour acceder à l'editeur vba
puis Insertion Module
Dans Module 1 copier ceci

Public Function NbOV(plage As Range, s As String) As LongDim n As Long, cel As Range, ts, k As Longn = 0For Each cel In plage  ts = Split(cel.Value, ",")  For k = 0 To UBound(ts)    If ts(k) = s Then n = n + 1  Next kNext celNbOV = nEnd Function

Dans la feuille la fonction
= NbOV(A1:A4;1) pour compter le nombre de 1 "purs" (séparés par une virgule du reste de la cellule) dans la plage A1:A4

Cdlmnt

ccm81 · Answer

@Vaucluse et Michel
Cordiales salutations  à tous les deux

... sauf si la liste de vos valeurs est en format texte et pas en format numérique, ce qui expliquerait par ailleurs le fonctionnement de NB.SI(...;"*..*") 

Si le contenu de la cellule est du genre 1,10,11,20,30 elle est forcément au format texte non?

Vaucluse · Answer

Exact CCM,(bonsoir)

On pourrait imaginer si il n'y a pas de risque de devoir compter plusieurs 1 dans la, même cellule:

=NB.SI(champ;1)+NB.SI(Champ;"*1,*")+NB.SI(Champ;"*,1")

qui totalisera le nombre de cellules avec 1 isolé + les 1 suivi d'un virgule + un éventuel 1 final précédé d'une virgule
Mais ne comptera pas le 1, s'il se présente plusieurs fois dans une cellule

donc en fait en si il y a risque de voir plusieurs fois 1, dans quelques textes, je crois qu'il n'y a que VBA pour résoudre le problème.

Bonne soirée
bien cordialement
 
Errare humanum est, perseverare diabolicum

Happy79_2 · Answer

Bon raisonnement, mais mes cellules sont au format standard.

Pour ma part, je croyais que les ** signifiaient "Contient", mais j'en comprends mainetenant que, parce qu'il y a une virgule, il le prend comme du texte et exclut les valeurs numériques comme 1 (uniquement). C'est pourquoi il a fallut que je j'additionne "1" à *1*; pour ensuite soustraire 10. 

=NB.SI($J$4:$J$501;"*1*")+NB.SI($J$4:$J$501;"1")-NB.SI($J$4:$J$501;"*10*") 

Lorsque j'en aurai l'occasion je transmettrai un fichier à partir de chez-moi.

J'aurai à ce moment une question plus complexe, c'est à dire àjouter une condition au calcul. 

En pssant, merci pour le temps qui vous passez à essayer de trouver cette mozus de formule.

NB.SI et Plusieurs valeurs dans une même cellule

12 réponses

Discussions similaires