Somme si avec conditions
laryburd
Messages postés
22
Statut
Membre
-
michel_m Messages postés 18903 Date d'inscription Statut Contributeur Dernière intervention -
michel_m Messages postés 18903 Date d'inscription Statut Contributeur Dernière intervention -
Bonjour,
Voici mon problème.
A B
1 105 0
2 208 1,2
3 0
4 0
5 516 2,4/1,2
6 609 1,3
7 0
8 805 0
9 0
En B10 par exemple; j'aimerai avoir le nombre de cellules numériques restantes après la cellule B5.
(B5 étant considéré comme du texte puisqu'elle contient le terme "/".)
Tout en ne comptant pas les cellules B7 et B9 car les cellules A7 et A9 sont vides.
Dans ce cas il est facile de voir à l'oeil que c'est "2".
Mais le soucis c'est que je dois le faire chaque jour à la main pour plus de 200 tableaux.
Avec évidemment un grand risque d'erreur.
Les valeurs de ces tableaux changent de place chaque jour mais restent toujours sous cette forme.
A savoir les nombres dans la colonne A et les valeurs correspondantes en colonne B.
Si quelqu'un a la solution, je le remercie d'avance
a+
Voici mon problème.
A B
1 105 0
2 208 1,2
3 0
4 0
5 516 2,4/1,2
6 609 1,3
7 0
8 805 0
9 0
En B10 par exemple; j'aimerai avoir le nombre de cellules numériques restantes après la cellule B5.
(B5 étant considéré comme du texte puisqu'elle contient le terme "/".)
Tout en ne comptant pas les cellules B7 et B9 car les cellules A7 et A9 sont vides.
Dans ce cas il est facile de voir à l'oeil que c'est "2".
Mais le soucis c'est que je dois le faire chaque jour à la main pour plus de 200 tableaux.
Avec évidemment un grand risque d'erreur.
Les valeurs de ces tableaux changent de place chaque jour mais restent toujours sous cette forme.
A savoir les nombres dans la colonne A et les valeurs correspondantes en colonne B.
Si quelqu'un a la solution, je le remercie d'avance
a+
A voir également:
- Somme si avec conditions
- Somme si couleur - Guide
- Formule somme excel ligne - Guide
- Excel cellule couleur si condition texte - Guide
- Somme en anglais excel - Guide
- Problème somme excel 0 ✓ - Forum Excel
20 réponses
Bonsoir,
Je n'ai pas bien compris si tu démarrais toujours après B5 ou s'il fallait recherche la 1ère cellule de B qui avait un /...
J'ai donc été au plus simple en considérant que le / était tjs en ligne 5.
Donc en B10:
=SOMMEPROD(NON(ESTVIDE($B$6:$B$9))*ESTNUM($C$6:$C$9)*1) =>2
Sinon il faudra rechercher le / avec un equiv() pour avoir sa ligne et utiliser decaler() pour remplacer les references B6:B9 et C6:C9
eric
Je n'ai pas bien compris si tu démarrais toujours après B5 ou s'il fallait recherche la 1ère cellule de B qui avait un /...
J'ai donc été au plus simple en considérant que le / était tjs en ligne 5.
Donc en B10:
=SOMMEPROD(NON(ESTVIDE($B$6:$B$9))*ESTNUM($C$6:$C$9)*1) =>2
Sinon il faudra rechercher le / avec un equiv() pour avoir sa ligne et utiliser decaler() pour remplacer les references B6:B9 et C6:C9
eric
Merci de me répondre.
Le signe "/" peut se trouver aussi bien en cellule B1 que B9, il peut également ne pas y en avoir du tout ou encore en avoir jusqu'a 9 maxi par jour.
Puisque les 9 cellules concernent une journée.
Puis le lendemaint 9 autres etc....
Ce qu'il me faut surtout c'est savoir le total de cellules contenant des valeurs num après la dernière valeur Texte de la journée.
Tout en ne tenant pas compte des cllules B7 B9 puisque A7 A9 sont vides.
Ce qui me donnera un Ecart pour le landemain.
Esemple; en fin de journée, je suis dans ce cas avec un écart 2 donc le lendemain matin mon tableau affiche 2 pour "Ecart en court".Grace à une macro.
J'espère avoir été clair dans ma réponse.
Merci d'avance.
Laurent
Le signe "/" peut se trouver aussi bien en cellule B1 que B9, il peut également ne pas y en avoir du tout ou encore en avoir jusqu'a 9 maxi par jour.
Puisque les 9 cellules concernent une journée.
Puis le lendemaint 9 autres etc....
Ce qu'il me faut surtout c'est savoir le total de cellules contenant des valeurs num après la dernière valeur Texte de la journée.
Tout en ne tenant pas compte des cllules B7 B9 puisque A7 A9 sont vides.
Ce qui me donnera un Ecart pour le landemain.
Esemple; en fin de journée, je suis dans ce cas avec un écart 2 donc le lendemain matin mon tableau affiche 2 pour "Ecart en court".Grace à une macro.
J'espère avoir été clair dans ma réponse.
Merci d'avance.
Laurent
Bonsoir Laurent, Eric,
Pour ma part, je me suis basé sur 9 opérations en partant de la ligne 1 et sur le cas où il pouvait y avoir plusieurs valeurs texte dans B1:B9
voici la formule proposée en B10
=SOMMEPROD(NON(ESTVIDE(DECALER(A1:A9;MAX(SI(ESTTEXTE(B1:B9);LIGNE(B1:B9)));0)))*1)
formule matricielle à valider par Ctrl+maj+ entrée (et non par entrée).
les cellules A10 à A19 doivent rester vides. Si tuas besoin de ces cellules alors la formule devient:
=SOMMEPROD(NON(ESTVIDE(DECALER(A1:A9;MAX(SI(ESTTEXTE(B1:B9);LIGNE(B1:B9)));0;9-MAX(SI(ESTTEXTE(B1:B9);LIGNE(B1:B9))))))*1)
toujours en matricielle
Enfin, si tu ne cpmmences pas en ligne 1, tu dois alors décrémenter "LIGNE(B1-B9)))" du nombre de lignes avant ta ligne de départ (si tu commences en ligne 3 (B3:B11) :
LIGNE(B3-B11)))-2
N'hésite pas au cas où!
Pour ma part, je me suis basé sur 9 opérations en partant de la ligne 1 et sur le cas où il pouvait y avoir plusieurs valeurs texte dans B1:B9
voici la formule proposée en B10
=SOMMEPROD(NON(ESTVIDE(DECALER(A1:A9;MAX(SI(ESTTEXTE(B1:B9);LIGNE(B1:B9)));0)))*1)
formule matricielle à valider par Ctrl+maj+ entrée (et non par entrée).
les cellules A10 à A19 doivent rester vides. Si tuas besoin de ces cellules alors la formule devient:
=SOMMEPROD(NON(ESTVIDE(DECALER(A1:A9;MAX(SI(ESTTEXTE(B1:B9);LIGNE(B1:B9)));0;9-MAX(SI(ESTTEXTE(B1:B9);LIGNE(B1:B9))))))*1)
toujours en matricielle
Enfin, si tu ne cpmmences pas en ligne 1, tu dois alors décrémenter "LIGNE(B1-B9)))" du nombre de lignes avant ta ligne de départ (si tu commences en ligne 3 (B3:B11) :
LIGNE(B3-B11)))-2
N'hésite pas au cas où!
Slt eric,
Merci d'avoir travailler afin de me venir en aide.
Ta Fonction marche sans défaut.
Mais le problème, c'est qu'elle me donne comme résultat "6" au lieu de "2".
Elle prend en compte les cellules B7 et B9 alors que A7 et A9 sont vides.
Je ne vois pas comment améliorer la chose.
Merci encore.
Laurent
Merci d'avoir travailler afin de me venir en aide.
Ta Fonction marche sans défaut.
Mais le problème, c'est qu'elle me donne comme résultat "6" au lieu de "2".
Elle prend en compte les cellules B7 et B9 alors que A7 et A9 sont vides.
Je ne vois pas comment améliorer la chose.
Merci encore.
Laurent
heu non, elle retourne bien 2 si bien saisie. Par contre je travaillais sur les colonnes B et C et non pas sur A et B.
Son défaut est que j'ai pris la ligne 5 fixe ne sachant pas tes conditions exactes.
Voir au post 5 la nouvelle formule en tenant compte.
Attendre aussi la réponse de michel qui est interessante si l'erreur de 1 au lieu de 2 est levée.
Bonne nuit
eric
Son défaut est que j'ai pris la ligne 5 fixe ne sachant pas tes conditions exactes.
Voir au post 5 la nouvelle formule en tenant compte.
Attendre aussi la réponse de michel qui est interessante si l'erreur de 1 au lieu de 2 est levée.
Bonne nuit
eric
Vous n’avez pas trouvé la réponse que vous recherchez ?
Posez votre question
Michel, il doit y avoir une petite erreur dans ta formule car tu retournes 1 au lieu de 2 dans l'exemple.
Bien que ta solution soit plus courte je livre quand même la mienne en 2 formules (que ça ne soit pas trop lourd) en attendant que tu modifies la tienne.
Bizarrement je vois que tu n'utilises que 2 fois la dernière ligne texte alors que moi 4 fois m'est necessaire... Peut-être une simplification à faire que j'aurais raté mais là il est un peu tard pour que je cherche ça... :-)
Donc en 2 formules:
Détection de la dernière ligne texte :
B10: =MAX(ESTTEXTE($B$1:$B$9)*LIGNE($B$1:$B$9)) Formule matricielle à valider par CTRL+Shift+Enter
B11: =SOMMEPROD(NON(ESTVIDE(DECALER($A$1;$B$10;0;9-$B$10;1)))*ESTNUM(DECALER($B$1;$B$10;0;9-$B$10;1)))
eric
Bien que ta solution soit plus courte je livre quand même la mienne en 2 formules (que ça ne soit pas trop lourd) en attendant que tu modifies la tienne.
Bizarrement je vois que tu n'utilises que 2 fois la dernière ligne texte alors que moi 4 fois m'est necessaire... Peut-être une simplification à faire que j'aurais raté mais là il est un peu tard pour que je cherche ça... :-)
Donc en 2 formules:
Détection de la dernière ligne texte :
B10: =MAX(ESTTEXTE($B$1:$B$9)*LIGNE($B$1:$B$9)) Formule matricielle à valider par CTRL+Shift+Enter
B11: =SOMMEPROD(NON(ESTVIDE(DECALER($A$1;$B$10;0;9-$B$10;1)))*ESTNUM(DECALER($B$1;$B$10;0;9-$B$10;1)))
eric
Bonjour Laurent, Eric
J'en suis juste après le petit déj... J'étudierai ça après les courses dans mon village !
Je joins la maquette pour bosser sur du réel: pour l'instant je ne vois pas d'erreur mais...
https://www.cjoint.com/?ckjEykJPw4
A presque tout de suite
Michel
J'en suis juste après le petit déj... J'étudierai ça après les courses dans mon village !
Je joins la maquette pour bosser sur du réel: pour l'instant je ne vois pas d'erreur mais...
https://www.cjoint.com/?ckjEykJPw4
A presque tout de suite
Michel
Re,
Ci joint 2° version
https://www.cjoint.com/?cklmLlus53
Je ne vois toujours pas mon erreur et pourtant je viens changer de lunettes... s'il y erreur, ca doit me crever les yeux et comme d'hab, je ne la vois pas!
modif de V2:
pour éviter la cellule intermédiaire, j'ai nommé la formule de recherche du dernier texte dans la colonne B1:B9 et transformé sommeprod en matricielle pure
Eric, en regardant ta formule, il me semble que le souci vient du 1°paramêtre de la fonction DECALER avec $A$1 soit une seule cellule tandis que je prend la zone A1:A9, ce qui me permet de décaler l'ensemble de la zone etainsi d'éviter le 2° prédicat avec ESTNUM
Sans être trop sûr de moi, je me méfie des dollars dans les matricielles.
Bon, qu'en penses Laurent?
A+
Michel
Ci joint 2° version
https://www.cjoint.com/?cklmLlus53
Je ne vois toujours pas mon erreur et pourtant je viens changer de lunettes... s'il y erreur, ca doit me crever les yeux et comme d'hab, je ne la vois pas!
modif de V2:
pour éviter la cellule intermédiaire, j'ai nommé la formule de recherche du dernier texte dans la colonne B1:B9 et transformé sommeprod en matricielle pure
Eric, en regardant ta formule, il me semble que le souci vient du 1°paramêtre de la fonction DECALER avec $A$1 soit une seule cellule tandis que je prend la zone A1:A9, ce qui me permet de décaler l'ensemble de la zone etainsi d'éviter le 2° prédicat avec ESTNUM
Sans être trop sûr de moi, je me méfie des dollars dans les matricielles.
Bon, qu'en penses Laurent?
A+
Michel
Bonjour michel,
Pas trop le temps de regardr non plus mais à priori plus d'erreur. Le mauvais résultat était retourné par la 1ère formule que tu as fourni.
Bonne journée
eric
Pas trop le temps de regardr non plus mais à priori plus d'erreur. Le mauvais résultat était retourné par la 1ère formule que tu as fourni.
Bonne journée
eric
Slt Eric et Michel.
Un grand Merci à vous deux pour vous êtes penché sur mon problème, il est évident que je n'y serait jamais arrivé sans vous.
Vous avez des connaissances que je n'ai pas en la matière.
Chaqu'un son métier, le miens est Chaudronnier Soudeur.
En tout cas Merci car la solution de Michel fonctionne nickel.
C'est un réel plaisir de trouver les réponses souhaitées sur ce forum, merci à l'administrateur.
Que de temps gagné lorsque l'on sait être patient.
Et dire que sur un autre site, d'un Mr portant le nom du tableaur de Microsoft, il me demandait 150€....
a+
Laurent
Un grand Merci à vous deux pour vous êtes penché sur mon problème, il est évident que je n'y serait jamais arrivé sans vous.
Vous avez des connaissances que je n'ai pas en la matière.
Chaqu'un son métier, le miens est Chaudronnier Soudeur.
En tout cas Merci car la solution de Michel fonctionne nickel.
C'est un réel plaisir de trouver les réponses souhaitées sur ce forum, merci à l'administrateur.
Que de temps gagné lorsque l'on sait être patient.
Et dire que sur un autre site, d'un Mr portant le nom du tableaur de Microsoft, il me demandait 150€....
a+
Laurent
Eric et Michel
Décidémment, j'ai encore besoin de vos lumières car j'ai d'autres tableaux du genre à traiter.
Mais la le problème est le même sauf que ces tableaux sont à l'horizontal au lieu d'être à la verticale.
J'ai bien essayé d'adapter votre fonction mais je n'y arrive pas.
Voici le tableau.
A B C D E F G H I
1 115 207 511 612 811
2 5,7/2,2 0 0 0 1,9 3,8/2,1 0 0 0
dans ce cas l'écart doit donner "1" puisque l'ont ne tiens pas compte de G2 et I2 car G1 et I1 sont vides.
Merci d'avance.
Laurent
Décidémment, j'ai encore besoin de vos lumières car j'ai d'autres tableaux du genre à traiter.
Mais la le problème est le même sauf que ces tableaux sont à l'horizontal au lieu d'être à la verticale.
J'ai bien essayé d'adapter votre fonction mais je n'y arrive pas.
Voici le tableau.
A B C D E F G H I
1 115 207 511 612 811
2 5,7/2,2 0 0 0 1,9 3,8/2,1 0 0 0
dans ce cas l'écart doit donner "1" puisque l'ont ne tiens pas compte de G2 et I2 car G1 et I1 sont vides.
Merci d'avance.
Laurent
Le plus simple est que tu sélectionnes ta zone et que tu fasses un copier/collage spécial en cochant 'transposé' ce qui te mettra ta matrice en vertical.
eric
eric
Bonsoir,
On ne peut pas transposer car les parametres (ligne colonne) passent à zéro et 1 pour les lignes
https://www.cjoint.com/?ckuGjUcF3h
Bonne semaine à tous les 2. Sympa ce fil, j' adore plancher à plusieurs sur un pb: c'est intéressant et enrichissant!
Amicalement
Michel.
PS: Mr.logiciel du m^me nom est bien connu pour ces pratiques; on ne peut rien dire, c'est légal... Il y en a d'autres qui piquent des solutions données gratos sur les forums et les revendent; et m^me des newbies- lusers (Lusers: Loosers + users) sur les forums qui s'approprient tes solutions pour frimer...
Mais à force, ca décourage les contributeurs.
On ne peut pas transposer car les parametres (ligne colonne) passent à zéro et 1 pour les lignes
https://www.cjoint.com/?ckuGjUcF3h
Bonne semaine à tous les 2. Sympa ce fil, j' adore plancher à plusieurs sur un pb: c'est intéressant et enrichissant!
Amicalement
Michel.
PS: Mr.logiciel du m^me nom est bien connu pour ces pratiques; on ne peut rien dire, c'est légal... Il y en a d'autres qui piquent des solutions données gratos sur les forums et les revendent; et m^me des newbies- lusers (Lusers: Loosers + users) sur les forums qui s'approprient tes solutions pour frimer...
Mais à force, ca décourage les contributeurs.
Slt Michel
Je viens de voir après vérif, que dans ton fichier, lorque je mets du texte en cellule I2, cla me donne 1 au lieu de 0.
Pour les autres cellules pas de problème.
Que dois-je changer?
Merci
Laurent
Je viens de voir après vérif, que dans ton fichier, lorque je mets du texte en cellule I2, cla me donne 1 au lieu de 0.
Pour les autres cellules pas de problème.
Que dois-je changer?
Merci
Laurent
Bonsoir,
essaies:
=SOMME(SI(NON(ESTVIDE(DECALER(A1:I1;0;col_txt;1;10-col_txt)));1))
il faut laisser vide la cellule J1
m^me punition avec les données en colonnes
En espèrant que...
essaies:
=SOMME(SI(NON(ESTVIDE(DECALER(A1:I1;0;col_txt;1;10-col_txt)));1))
il faut laisser vide la cellule J1
m^me punition avec les données en colonnes
En espèrant que...
Milles merci Michel, tu es mon sauveur.
Vraiment trop sympa.
Merci d'avoir passer tout ce temps pour m'aider.
Cà marche à merveille.
a+
Laurent
Vraiment trop sympa.
Merci d'avoir passer tout ce temps pour m'aider.
Cà marche à merveille.
a+
Laurent
