SOMME.SI.ENS faire un tri sur une partie seulement d'une chaine de caractè
Bonjour à tous,
Voilà mon problème :
je cherche le total d'une colonne en fonction de critère dépendant d'une autre colonne, je m'explique :
colonne A valeurs texte contenant :
1 Du 01/07/2019 (11:54) Au 01/07/2019 (14:32)
2 Du 01/07/2019 (19:02) Au 01/07/2019 (23:42)
3 Du 02/07/2019 (06:28) Au 02/07/2019 (15:32)
4 Du 02/07/2019 (19:27) Au 02/07/2019 (23:56)
colonne B valeurs numériques :
1 1408.30
2 2576.60
3 2851.30
4 2297.00
je veux faire le total des valeurs de la colonne B seulement si l'heure de début de service est inférieure à 18:00
c'est à dire seulement si le 16eme et 17eme caractère de la colonne A est strictement inférieur à 18 (par exemple)
=SOMME.SI.ENS(B:B;A:A;"???????????????18*") me renvoie uniquement la somme des lignes contenant "18" en 16eme et 17eme position, en clair, je veux obtenir le total de la ligne 1 et de la ligne 2 dans mon exemple.
=SOMME.SI.ENS(B:B;A:A;"?????????????????*<???????????????18*") ne fonctionne pas ...
Est-ce que quelqu'un peut m'aider ?
Merci d'avance
Alain
Bonjour,
Avec un point plutôt qu'une virgule en B :
=SOMMEPROD((SUBSTITUE($B$2:$B$5;".";","))*(CNUM(STXT($A$2:$A$5;16;2))<18))Avec une virgule :
=SOMMEPROD(($B$2:$B$5)*(CNUM(STXT($A$2:$A$5;16;2))<18))Un grand merci Oyobrans,
ça faisait deux heures que je cherchais ...
Question subsidiaire :
Maintenant si je veux faire une somme en imbriquant plusieurs critères ( le total des services ayant commencé avant 18h ET entre le 01/07/2019 et le 10/07/2019 par exemple) ?
Une soluce ?
Tu devrais joindre un fichier quand tu as une demande, ça évite d'avoir à le recréer.
=SOMMEPROD((SUBSTITUE($B$2:$B$5;".";","))*(CNUM(STXT($A$2:$A$5;16;2))<18)*(CNUM(STXT($A$2:$A$5;4;10))>=DATE(2019;7;1))*(CNUM(STXT($A$2:$A$5;4;10))<=DATE(2019;7;10)))ou si tes dates sont en J1 et J2 :
=SOMMEPROD((SUBSTITUE($B$2:$B$5;".";","))*(CNUM(STXT($A$2:$A$5;16;2))<18)*(CNUM(STXT($A$2:$A$5;4;10))>=J1)*(CNUM(STXT($A$2:$A$5;4;10))<=J2))Encore merci pour ton aide !
J'ai joins le fichier.
J'ai copié-collé ta formule en remplaçant les adresses (les données sont sur une autre page du classeur). L'instruction substitute n'est à mon avis pas vraiment nécessaire vu que les chiffres sont avec une virgule mais je l'ai laissée quand même, l'enlever ne change pas le message d'erreur de toute façon, pas plus que remplacer les noms "datedepart" et "datefin" par la fonction DATE().
Une idée de l'endroit où j'ai merdé ?
En utilisant un tableau (Insertion > Tableau) et en supprimant la ligne Total :
=SOMMEPROD(Tableau1[Total TTC Net]*(CNUM(STXT(Tableau1[Services];16;2))<18)*(CNUM(STXT(Tableau1[Services];4;10))>=datedepart)*(CNUM(STXT(Tableau1[Services];4;10))<=datefin))CHAMPION DU MONDE Oyobrans !!!!!!
un super grand merci
et en plus je suis ton 1000ème post sur le forum !!!!
Ca s'arrose
Santé !
Bonjour Oyobrans,
Je reviens vers toi avec une petite question pratique :
Si dans une formule du type évoqué, je souhaite changer le nom du tableau sans avoir à modifier la formule à chaque fois, comment je peux faire ?
En l’occurrence, changer le nom du tableau nommé : tableau1 par le contenu d'une cellule, la cellule C2 par exemple
=SOMMEPROD(Tableau1[Total TTC Net]*(CNUM(STXT(Tableau1[Services];16;2))<18)*(CNUM(STXT(Tableau1[Services];4;10))>=datedepart)*(CNUM(STXT(Tableau1[Services];4;10))<=datefin))
ce qui permettrait de renvoyer le résultat d'autres tableaux de la feuille sans changer toutes les formules ...
Ca fait des heures que je galère ...
Merci d'avance.
Bonjour Ecluse34, Oyobrans
Tu peux le faire en utilisant la fonction INDIRECT()
Si nom tableau en C2, voici la formule
=SOMMEPROD(INDIRECT( C2 &"[Total TTC Net]")*(CNUM(STXT(INDIRECT( C2 &"[Services]");16;2))<18)*(CNUM(STXT(INDIRECT( C2 &"[Services]");4;10))>=datedepart)*(CNUM(STXT(INDIRECT( C2 &"[Services]");4;10))<=datefin))@+
Un grand merci BrunoM45,
ça marche nickel !!!
Comme on dit : il vaut toujours mieux un qui sait que 25 qui cherchent ...
trop cool
Bonsoir,
Pas mieux
Bonjour a tous,
J'ai une autre question sur SOMEPROD, alors je crois que je vais ouvrir un nouveau sujet.
C'est mieux non ?
Alain