[excel] NB si à deux critères? Résolu/Fermé

Question

Bonjour, 
je voudrais faire la formule NB si avec deux critères. je dois ajouter un critère chiffré et un critère lettré. 
Merci d'avance

gbinforme · Accepted Answer

bonjour Cris

essaie ceci si j'ai compris ton souci : il faut que les 2 plages aient la même taille et que les valeurs "x" et "y" soient situées sur la même position relative.

=SOMME((plage1="x")*(plage2="y")*1)
c'est une formule matricielle à valider par ctrl +maj + entrée

JvDo · Answer

Bonjour,

personnellement j'utilise la fonction matricielle {=Somme(si((mazone1=moncritèrenum)*(mazone2=moncritèrealpha);1;0))} les accolades sont obtenues par une validation CTRL+SHIFT+ENTER de la formule.

cordialement

gbinforme · Answer

bonjourje voudrai faire la formule NB si avec deux critères.tu peux toujours faire : nb.si(plage;"x")+nb.si(plage;"y")si c'est bien pour compter les occurences.

Alf-Red · Answer

la formule de gbin est bien plus simple et répond aussi a ce que je voulais faire, 
mais j'aurais préféré trouver un opérateur "ou " 
pour une formule de ce type: 

=countif(plage,"text1" ou "text2") 

désolé j'utilise une version en anglais de excel.. 
le countif = nb.si 
et la virgule remplace le ; 
évidemment le "ou" serait a remplacer avec un opérateur (un caractère) du type || ou un truc du genre. 
qui permettrait d'éviter de retaper toute la séquence countif(plage1,xxx)+countif(plage1,xxx) 
alors qu'on fait appel a la même fonction et qui plus est, sur la même plage...
Configuration: Windows XP Internet Explorer 6.0

jlmartinez · Answer

Bonjour 

Pour être plus précis je souhaite dans le cadre d'un planning calculé le nembre d'heure mensuel que chaque personne fait.

ces heures ont une equivalence en lettre par exemple M (pour matin) = 7 H de 7h du matin à 14h, je dois attribué plusieurs M à plusieurs personnes dans différent lieu j'ai 6 lieux. Aussi pour identifier l'horaire à faire et le lieu j'utilise : M1,M2...M6. chaque association du M et d'une lettre vaut 7 heures aujourd'hui la formule pour avoir la somme de tout les M sur une ligne je fais formule suivante :
NB.SI(plage;"M1")*7+NB.SI(plage;"M2")*7+NB.SI(plage;"M3")*7+NB.SI(plage;"M4")*7+NB.SI(plage;"M5")*7+NB.SI(plage;"M6")*7

J'ai une dizaine de lettre différente encore a placer de la même manière. Je ne sais pas si c'est possible mais j'ai besoin de faire plus court car au bout 6 lignes de formules je ne peux plus enregistrer ma page.
existe-t-il quelque chose du type :
NB.SI(plage;"M1"+"M2"+...+"M6")*7 qui permettrait qu'à chaque fois qu'un M... existe la valeur 7 soit ajouté au total final ? et de la même manière pour B,N,L,S,D.... En sachant que B=6.5: N=10, total final sur la même cellule.

j'espère avoir été plus clair.
Merci d'avance de votre réponse
Cordialement
jean-louis

gbinforme · Answer

bonjour

j'utilise : M1,M2...M6. chaque association du M et d'une lettre vaut 7 heures...
NB.SI(plage;"M1")*7+NB.SI(plage;"M2")*7+NB.SI(plage;"M3")*7...J'ai une dizaine de lettre différente encore.

Comment faire simple quand on a épuisé la complexité, il me semble que c'est l'objet de la question.
C'est pourtant plus simple de faire ainsi :
=NB.SI(plage;"M*")*7

Toutes les valeurs qui commencent par "M" seront prises en compte avec le caractère "*" et si tu ne veux que limiter sur un caractère quelconque il faut utiliser "?".

Dom1984 · Answer

Bonjour
j'ai également un problème de formule Excel
Voilà, j'ai deux colonnes:

A            B

X           1
Y           0
Z           0
Z          4
Y         0
X         2

Je cherche une formule qui pour chaque lettre de la colonne A me compte le nombre de cases différentes de 0.
Ex: pour X: la formule doit inscrire 2, pour Y: 0, pour Z: 1

Pouvez vous m'aider?

Je vous remercie!!!

gbinforme · Answer

bonjour

Je suppose que tes données sont de A1 à B24.
En D1 tu saisis le code que tu veux totaliser.
Dans la cellule de totalisation tu mets la formule suivante :
=SOMME(($A$1:$A$24=$D$1)*($B$1:$B$24<>0)*1)
et tu valides avec CTRL + MAJ + ENTREE cette formule matricielle
qui sera ainsi mise entre crochets { formule } et fonctionne très bien.

Si tu veux additionner les sommes de la colonne B, tu peux faire de la même façon :
=SOMME(($A$1:$A$24=$D$1)*($B$1:$B$24))

Tiens nous au courant des tests...

gbinforme · Answer

bonjour simo,

Si tu avais essayé de comprendre les formules précédentes tu aurais vu que c'était assez simple :
=SOMME(($B$9:$B$86=1)*($T$9:$T$86=1)*1)

et tu valides avec CTRL + MAJ + ENTREE cette formule matricielle
qui sera ainsi mise entre crochets { formule } et fonctionne très bien.


Excel regarde dans chacune des plages, qui doivent être de même taille, le nombre de cellules de même rang qui satisfont au critère (=1) et pour avoir le nombre on multiple par 1 (*1).

simo · Answer

si j'ai essayé mais ça donne 0 au lieu de 2

gbinforme · Answer

bonjour 

si j'ai essayé mais ça donne 0 au lieu de 2 

Vérifie la formule que tu as saisie et elle doit être incorrectement encodée. 
Toutes les parenthèses sont obligatoires à la bonne position, mais je viens de vérifier et c'est correct. 

L'addition se fait sur les cellules de même rang, c'est-à-dire qu'il faut à la fois b10=1 et t10=1, sinon c'est zéro. 
sinon, si tu veux les 1 sur les deux plages quel que soit la position, c'est ceci : 
=SOMME(($B$9:$B$86=1)*1)+SOMME(($T$9:$T$86=1)*1)

à toi de voir ce que tu recherches... et tiens nous au courant. 


toujours zen

simo · Answer

Merci, c logique cke tu m'a expliké là mé ça marche pas non plus si tu veux jt'envoi le doc poour voir toi même

gbinforme · Answer

bonjour simo_lem,

Dans la colonne T tu as des formules dans le style :
=SI(S9>21;"1";SI(S9>10;"2";SI(S9>6;"3";SI(S9>3;"4";SI(S9>1;"5";" ")))))
Donc tu obtiens une valeur "texte" égale à "1" et en colonne B tu tapes le "chiffre" 1, donc pour que ta formule fonctionne, il faut l'écrire ainsi :
=SOMME(($B$9:$B$86=1)*($T$9:$T$86="1")*1)
En gardant les bonnes définitions de format, tu verras que la formule fonctionne correctement !
En ayant les formules de la colonne T ainsi écrites, la formule que je t'avais donnée aurait parfaitement fonctionné :
=SI(S9>21;1;SI(S9>10;2;SI(S9>6;3;SI(S9>3;4;SI(S9>1;5;" ")))))
Il ne faut jamais mélanger les valeurs numériques avec les valeurs alphanumériques car elles ne sont surtout pas équivalentes.

simo_lem · Answer

Bonjour,  
merci ça marche mais tout à fait juste au moins c'est la première fois que ça me donne un chiffre, le problème c'est que ça me donne un 1 alors que normalement ça devrait donner 2, si tu regarde bien il y a 2 fois où l'on trouve 1 sur les 2 plages donc ça doit me donné 2, aussi si je veux mettre la formule sur une ligne autre que la ligne 9 ça donne #valeur# pourtant c la valeur absolu des plages qu'on mets 
merci tout de même pour ton coup de main.

gbinforme · Answer

bonjour 

merci ça marche mais tout à fait juste 

Bon là il y a réellement un problème car dans un tableur comme en arthmétique ou en mathèmatiques, les résultats sont justes ou faux mais jamais "pas tout à fait justes"

En colonne B tu as trois fois la valeur 1 lignes 9, 10, 11. 
En colonne T tu as 25 fois la valeur 1 lignes 9, 10, 12, 14, 22, 23, 24, 25, 27, 31, 32, 34, 35, 38, 39, 42, 53, 55, 56, 57, 59, 60, 61, 65 ,67 
Et tu constates qu'il n'y a que les lignes 9 et 10 communes. 
Et en mettant n'importe où la formule suivante, sauf dans les plages concernées, j'obtiens bien 2. 
=SOMME(($B$9:$B$86=1)*($T$9:$T$86="1")*1)

aussi si jveux mettre la formule sur une ligne autre que la ligne 9 ça donne #valeur# pourtant c la valeur absolu des plages qu'on mets 

Si tu saisis comme moi sur ton classeur sur n'importe qu'elle colonne, en dehors de B ou T, la formule suivante : 
=SOMME(($B$1:$B$65000=1)*($T$1:$T$65000="1"))*1
Tu obtiens aussi 2, ce qui me parait normal et si tu obtiens #valeur# c'est que tu ne saisis pas correctement la formule, mais je n'ai pas réussi a obtenir cette erreur, même si j'ai pu en provoquer d'autres, alors je suis incapable de te donner l'erreur de manipulation commise. Par contre, si tu mets : 
=SOMME((B:B=1)*(T:T="1"))*1)
tu obtiens  "#NOMBRE!" ce qui est normal car la plage ne peux être une colonne entière. 

Donc comme je te met les formules en format correct, tu fait un copier(ctrl + c) de ma ligne de formule et tu fais coller (ctrl+v) sur ton classeur dans la cellule voulue et tu valides la formule par CTRL+ MAJ + ENTER, car c'est une formule matricielle. Et la valeur sera totalement juste ! 



toujours zen

simo_lem · Answer

Bonjour,  

merci à tous , je vais essayer et je vous tiens au courant

simo_lem · Answer

Bonjour, 
c bon ça marche bien Merci Gbinforme.
en fait cke jé fé com coneri c ke j'ai changer le caractère 1 du t9 en  nombre mais jlé pas appliké sur toute la colone.
grand merci tu m'as rendu un grand service.

bem · Answer

Bonjour à tous,
Une suggestion différente et que je trouve plus pratique à l'usage (et plus rapide) : sommeprod.
=sommeprod(--(condition1);--(condition2);...)
exemple : =sommeprod(--(Toulon!$B$1:$B$65535=1);--('La Garde'!$T$1:$T$65535=1))
(pas besoin de formule matricielle => un simple "entrée" sans ctrl-maj suffit)

Par ailleurs, si vous avez de grosses plages, je vous conseille de les nommer (menu Insertion-Nom), en plus ça clarifie l'expression. Vous pouvez même calculer l'expression qui définit vos plages nommées, mais c'est une autre histoire... (Essayez =decaler($A$1;0;nbval($A:$A)) pour couvrir toutes la partie utilisée d'une colonne.)

fredoche · Answer

bonjour a tous

Je vois que vous ete bien calle sur Excell. Vous pourriez peut etre m aider.

Je desire moi aussi utiliser la formule nb.si mais avec un critere non precis. C'est a dire que je veux calculer le nombre de cellule ou il y aurait par exemple le mot "Bonjour" dans une phrase entiere ?

Des idees

Merci d'avance

Vloups · Answer

Bonjour
J'ai 2 colonnes : la première contenant un critère intitulé profil (A, B, C, D), la deuxième contient un autre critère intitulé fonction (1, 2, 3, 4)
J'aimerais avoir la formule qui me permet de sélectionner et de compter tous les A2 et B2... Est-ce que quelqu'un pourrait m'aider ?

A 2
B 4
C 2
A 1
B 2
C 3
D 4
A 2
A 1

gbinforme · Answer

bonjour


soit une formule SOMMEPROD soit une SOMME matricielle
=SOMMEPROD((A1:A9="A")*1;(B1:B9=2)*1)
ou
=SOMME((A1:A9="A")*(B1:B9=2))
à valider par les touches ctrl + entrée + majuscule simultanées

Cmoi · Answer

Bonjour,

J'aimerai pouvoir additionner les chiffres de la colonne B quand la condition en lettre de la colonne G est remplit.
D'avance merci.

B     G              H

10   Olivier
5     Marc
25   Benoit
30   Olivier
50   Denis
10   Marc

                  Olivier 40
                  Marc   15

lilou13003 · Answer

salut à tous,

moi g un petit souci avec une fonction nb.si j'aimerai chercher un nombre compris entre 5min et 20min par exemple. Mon problème est que je n'arrive pas à placer mes signes (>00:05:00 et <00:20:00 ) pour arriver aux résultats que je souhaite. 

Merci de m'aider

lilou13003 · Answer

Salut à tous

J'ai un petit souci avec une fonction nb.si. Je voudrai rentrer comme critère (entre 5 min et 20 min). Pour cela , j'ai essayé de placer les signes tel que >00:05:00 & <00:20:00 mais cela ne marche pas. aidez moi svp *

Merci

Cmoi · Answer

Bonjour,

Étant satisfait du résultant que me donne 
=SOMME.SI(G1:G9;"=A";B1:B9), sur une feuille,
je voudrais pourvoir étendre la fonction pour plusieurs feuilles qui irai chercher dans le colonnes G1:G9;B1:B9 de toutes mes feuilles.

Je m'explique:
j'ai 12 feuilles qui represente les 12 mois de l'année.
Je si rentre la fonction suivante:
=SOMME.SI(Janvier:Decembre!G1:G9;"=A";Janvier:Decembre!B1:B9)
J'ai un beau: #VALEUR!

D'avance merci pour le coup de main.

gbinforme · Answer

bonjour

J'ai un beau: #VALEUR! 

C'est normal car cette fonction ne peut pas être utilisée en 3D.

sbvf · Answer

Bonjour, 

J'ai un petit problème avec Excel donc j'ai besoin d'aide

dans un onglet A, jai une liste de documents ici je n'ai représenté que le document 1, dans une autre colonne on connait l'état (OK/KO) du doc en fonction d'autre chose. dans l'onglet B, je dois vérifier combien il y a de OK pour le doc 1 et combien de KO. mais je n'arrive pas à trouver la fonction adéquate.
colonne A        colonne B
doc 1              OK
doc 1              OK
doc 1              KO
doc 1              OK
doc 1              KO
doc 1              OK

merci d'avance pour votre aide

sbvf · Answer

bemerit,

je viens d'essayer sommeprod(--(a1:a6="doc 1");--(b1:b6="OK")) mais cela m'affiche #NOMBRE!

pour les autres cas, je ne veux pas d'un calcul ponctuel car il faut qu'à chaque modification effectuée, les données se mettent à jour toutes seules.
Qu'est ce qu'un tableau croisé dynamiques? ca a lair assez dur à mettre en place.

merci

oliv · Answer

la fonction bdnbval peut sans doute résoudre ton problème. Avec elle, tu peux compter en imbriquant les "et" et les "où " sans aucune limitation

VIRG · Answer

Bonjour,

Moi aussi j'ai besoin de vos lumières sur la formule à 2 critères.
Je vous explique:
J'ai un tableau ou j'enregistre tous mes RDV. Dans la première colonne j'ai le TYPE DE RDV et dans la deuxième colonne le LIEU DU RDV. Maintenant, j'ai créé un deuxième onglet STATS sur mon fichier excel afin de faire des statistiques. Je voudrais savoir par exemple combien de rdv PROSPECTION (Colonne 1) sur FINISTERE (Colonne 2).

Voici la formule que j'ai essayé mais qui hélas ne veut pas fonctionner!!!
=NB.SI('Origine rdv 2008'!K25:K3000;'Origine rdv 2008'!K306)+NB.SI('Origine rdv 2008'!L25:L3000;'Origine rdv 2008'!L633)

Légende:
Origine rdv 2008 = nom du tableau
K25:K3000 = colonne 1 (donc type de rdv)
K306 = critère que j'ai choisi (ici prospection)
L25:L3000 = colonne 2 (donc lieu du rdv)
L633 = critère que j'ai choisi (ici finistère)

Je suis bloquée je ne peux plus avancée alors merci de votre aide.

virg · Answer

J'ai réussi avec la formule SOMME SI et en faisant CTRL+ Shift+ entrée, je ne faisais pa shift mais maj.
Encore merci.

Cordialement,

Virginie

X'tof · Answer

Bonjour,

J'ai un cas que je n'arrive pas à solutionner :o(

J'ai un tableau avec des colonnes correspondant aux douze mois de l'année. Des lignes correspondant à des établissements. A l'intersection, j'ai un taux.

Je comptais faire un NB.SI pour déterminer le nombre d'agence dont le taux est compris entre 2 valeurs (une mini, une maxi). La complexité principale (et j'imagine qu'il s'agit essentiellement d'un problème de syntaxe ?) est que j'ai besoin que ces bornes maxi mini soient gérées dans le NB.SI en tant que variable

     A             B              
1                 Mois          
2 Dépt	   1	    

3 Société	  0.52%	    --> valeurs de références

4 20%	  0.63%	    --> Borne maxi (en B4) qui résulte du calcul B3*A4
5 -20%	  0.42%	    --> Borne mini (en B5) qui résulte du calcul B3*A5

6 N48	  0.54%	  A partir d'ici ce sont les dizaines de lignes dont je souhaite dénombrer, par mois, le nombre 
7 M63	  0.59%	  de valeurs en colonne B qui s'inscrivent entre les deux bornes
8 M53	  0.58%	  L'idée est de pouvoir faire varier les % (A4 et A5) et que la MAJ du dénombrement se 
                                  fasse automatiquement

Espèrant avoir été clair, je remercie tous ceux qui se pencheront sur ce cas

gbinforme · Answer

bonjour

Tu devrais plutôt utiliser SOMMEPROD car NB.SI n'autorise qu'un test.

X'tof · Answer

OK j'y suis parvenu avec la formule : =SOMMEPROD((C$11:C$120<=C$9)*(C$11:C$120>=C$10)) pour dénombrer les cas qui sont dans ma fourchette, et =SOMMEPROD((C$11:C$120>=C$9)+(C$11:C$120<=C$10)) pour ceux qui n'y sont pas ! Merci de ton aide :-)

F · Answer

Bonjour,

je souhaite faire une recherche par nom, et faire une somme en fonction des sommes correspondants aux noms cherchés:
Exemple 
colonne E plage E1:E17 : il y a les noms.
Colonne D plage D1:D17: il y a des montants ( genre 5 euros, 1.5 euros ...) qui correspondent aux dépenses des personnes mentionnées en colonne E.
Je veux faire la somme des montants pour chaque personne en une seule formule.
Pouvez vous 'maider ?

sucrette · Answer

A                   B 
1 Inscriptions    Commandes 
2 27/03/2008   03/11/2008 
3 07/11/2008   25/03/2009 
4 18/06/2009   26/06/2009 

Heu... voilà, en fait j'ai encore une requete... 

Si je veux obtenir le nombre de commandes passées par les clients inscrits avant le 01/01/2009... 

Comment puis-je faire avec la formule NB.SI(A2:A4;"01/01/2009") ??? 

Merci beaucoup...

Okaya · Answer

Bonjour,

Je rencontre un problème sur Excel et étant un peu débutant, je galère.

Voici mon tableau :

A___B_________C__   
1__27/05/2009__HLT
2 __27/05/2009__HLT
3 __27/05/2009__FPP
4 __28/05/2009__FPP
5 __28/05/2009__HLT

J'aurais besoin d'une formule qui me donne, par exemple, le nombre de HLT en C pour B=27/05/2009.
En l'occurence, la réponse serait 2 dans cet exemple.
Avec NB.SI je peux avoir le nombre de 27/05/2009, le nombre de HLT mais il me faudrait une formule pour avoir le nombre de C=... SI B=...

J'espère avoir été assez précis dans ma demande.

D'avance un grand merci pour votre aide !

gbinforme · Answer

bonjour

Utilises SOMMEPROD qui permet les 2 critères
=SOMMEPROD((B1:B1000=DATEVAL("27/05/2009"))*(C1:C1000="HLT"))

[excel] NB si à deux critères?

38 réponses

Discussions similaires