Microsoft 365 Addition des nombres dans une cellule avec du texte

Dreyz

XLDnaute Nouveau
Bonjour à tous,

Je tente en vain d'additionner des chiffres de ma colonne A qui contient des nombres et des lettres, vers ma colonne B
Exemple :
A1 :
benoit x 1, eric x 1, nathalie x 1, marc x 15
J'aimerais avoir en B = 18

A2 :
jean-jacques x 1, eric x 1, marie x 1
En B = 3

A3 :
jean-jacques x 1, eric x 5, marie x 1, marie x 1, marc x 1, sylvie x 1, , flavien x 5
En B = 15

Je cherche donc à trouver une formule pour additionner les chiffres se trouvant après "x ".
Je précise que je suis essentiellement sous google sheet, ayant testé une formule type (je débute sur les tableurs, j'avais trouvé cela sur le net)

=SOMMEPROD(SIERREUR(1*STXT(A1;LIGNE(INDIRECT("1:"&NBCAR(A1)));1);0))

Mais qui ne prend pas en compte les valeurs supérieurs à 9. Exemple au lieu d'avoir d'avoir une somme de 18 en B1 , il me renvoie 9 (1+1+1+1+5).


Merci à vous,
Nicolas.
 
Dernière édition:
Solution
Re,
Puis je me suis souvenu que j'avais des archives alors j'ai mis un masque et soulevé la poussière et bim!
=SOMME(SIERREUR((STXT(SUBSTITUE(SUBSTITUE(A1;",";"");" ";REPT(" ";NBCAR(A1)));1+NBCAR(A1)*(LIGNE(A$1:INDEX(A:A;NBCAR(A1)-NBCAR(SUBSTITUE(A1;" ";""))+(A1<>"")))-1);NBCAR(A1)))+0;0))
Il faudrait un volontaire sous XL2K10 pour la tester ;)

On peut faire plus courte, en B1 :
VB:
=SOMME(SIERREUR(STXT(SUBSTITUE(A1;" ";REPT(" ";NBCAR(A1)));(LIGNE(INDIRECT("1:"&(NBCAR(A1)-NBCAR(SUBSTITUE(A1;" ";))+1)))-1)*NBCAR(A1);NBCAR(A1))*1;))
@ valider par Ctrl+Maj+Entrée
@ tirer vers le bas

Valide pour toutes les versions à partir de 2007.

Noter que sous Excel pour Microsoft 365 on n'a pas...

Staple1600

XLDnaute Barbatruc
Re,

=>mapomme
Et là, cela fonctionne toujours pas?
VB:
Const s = "benoit x 1, eric x 1, nathalie x 1, marc x 15"
Sub test_Formule()
f = _
"=SUM(IFERROR((MID(SUBSTITUTE(SUBSTITUTE(A1,"","",""""),"" "",REPT("" "",LEN(A1))),1+LEN(A1)*(ROW(A$1:INDEX(A:A,LEN(A1)-LEN(SUBSTITUTE(A1,"" "",""""))+(A1<>"""")))-1),LEN(A1)))+0,0))"
[A1] = s
[B1].FormulaArray = f
End Sub
Je viens de refaire le test.
 

soan

XLDnaute Barbatruc
Inactif
@Staple1600

je viens d'essayer ta formule matricielle ; je te confirme qu'elle marche très bien
sur mon Excel 2007, et qu'elle donne bien les 3 résultats attendus : 18 ; 3 ; 15

perso, j'préfère ma fonction personnalisée, car j'aime ni les formules à rallonge,
ni les formules matricielles. (c'est mon ancienne habitude de BASICA et GWBASIC ! :D)


--------------------------------------------------------------------------------------

ajout : contrairement à ce qu'a écrit Staple, je précise qu'il faut valider
sa formule avec Ctrl Maj Enter et pas avec Ctrl+TRUC+ENTER ; c'est
pour ça que @mapomme n'y est pas arrivé au départ : il a cherché
en vain la touche TRUC (qui n'existe ni sur PC, ni sur McIntosh).


soan
 
Dernière édition:

Staple1600

XLDnaute Barbatruc
Re
=>soan
Que ce soit ta fonction personnalisée ou la mienne
(donc solution VBA), elles ne fonctionnent pas sur Google Sheets.

Les formules de R@chid ,elles, fonctionnent sur Excel et/ou Google Sheets.

=>mapomme
Je te fais une tisane avant d'aller rejoindre Morphée? ;)
Moi, je me fais un café.
 

Staple1600

XLDnaute Barbatruc
ajout : contrairement à ce qu'a écrit Staple, je précise qu'il faut valider
sa formule avec Ctrl Maj Enter et pas avec Ctrl+TRUC+ENTER ; c'est

Je l'ai précisé mais il faut savoir lire entre les lignes... :rolleyes:

Bonsoir mapomme
Elle fonctionne chez moi (sur mon Treize)
NB: Validation avec CTRL+TRUC+ENTER matrice et cie ;)
(comme la formule à R@chid)[
 

R@chid

XLDnaute Barbatruc
Bonsoir @ tous,
désolé de ce retour tardif
Voir ICI ce que dit Krosoft. ;)
Oui mapomme, je savais que la fonction FILTRE.XML existe depuis la version 2013, d'où ma demande de confirmation, et surtout qu'un jour j'ai téléchargé un fichier (si je me rappelle bien) de notre chère amie Chris où elle utilisait bien cette fonction dans une formule, mais quand je l'ai ouvert sous 2016 ou 2019 (je ne me rappelle pas) ça n'a pas fonctionné chez moi.


@Staple1600
Oui comme il a dit mapomme, si ça marche chez toi, ça ne veut pas dire qu'elle va fonctionner chez des autres même si c'est la même version.

SOMME(SPLIT()) sous GoogleSheet fonctionne très bien, je la connais très bien, d'où j'ai essayé de faire de même avec FILTRE.XML sous Excel.
 

R@chid

XLDnaute Barbatruc
Re,
Puis je me suis souvenu que j'avais des archives alors j'ai mis un masque et soulevé la poussière et bim!
=SOMME(SIERREUR((STXT(SUBSTITUE(SUBSTITUE(A1;",";"");" ";REPT(" ";NBCAR(A1)));1+NBCAR(A1)*(LIGNE(A$1:INDEX(A:A;NBCAR(A1)-NBCAR(SUBSTITUE(A1;" ";""))+(A1<>"")))-1);NBCAR(A1)))+0;0))
Il faudrait un volontaire sous XL2K10 pour la tester ;)

On peut faire plus courte, en B1 :
VB:
=SOMME(SIERREUR(STXT(SUBSTITUE(A1;" ";REPT(" ";NBCAR(A1)));(LIGNE(INDIRECT("1:"&(NBCAR(A1)-NBCAR(SUBSTITUE(A1;" ";))+1)))-1)*NBCAR(A1);NBCAR(A1))*1;))
@ valider par Ctrl+Maj+Entrée
@ tirer vers le bas

Valide pour toutes les versions à partir de 2007.

Noter que sous Excel pour Microsoft 365 on n'a pas besoin de validation matricielle.


Cordialement
 

Staple1600

XLDnaute Barbatruc
Bonjour R@chid

=>R@chid
Si, si, elle fonctionne. C'est moi qui avait merdoyé. Mille excuses.
Il est temps que j'aille au lit.
@Staple1600
Oui comme il a dit mapomme, si ça marche chez toi, ça ne veut pas dire qu'elle va fonctionner chez des autres même si c'est la même version.
On parle de même version et je parlais de ma formule pas de la tienne.
Je reprécise donc:
Si j'achète un Excel 2013 (qu'il soit Pro, Etudiant ou PME), j'aurais bien accès aux mêmes fonctions Excel, non ?
Donc sur un autre Excel 2013 dont le propriétaire serait en train de siroter un café (avec une paille) à Nankin, Tombouctou ou Vezoul, tout en faisant un CTRL+MAJ+ENTER avec ma formule, la verra fonctionner.

NB: Je vais de ce pas essayer ta 3ième formule.

PS: C'est toi qui a demandé à ce qu'on teste ta formule avec FILTRE.XML sur Excel 2013.
(Ce que j'ai fait)
 
Dernière édition:

Staple1600

XLDnaute Barbatruc
Re

=>R@chid
Test OK avec ta 3ième formule
Mais c'est logique puisque la mienne fonctionnait ;)
D'ailleurs tu le dis toi-même ;)
Valide pour toutes les versions à partir de 2007.
Toutes versions d'Excel possédant SOMME, SUBSTITUE, REPT etc verra nos formules fonctionner. C'est tout ce que je voulais dire ;)


Sinon pourrais-tu expliquer en détail le fonctionnement de ta superbe formule avec FILTRE.XML ?
Merci, car j'ai beau me creuser les méninges.
Je ne comprends pas comment elle fonctionne.
 

Discussions similaires

Statistiques des forums

Discussions
313 198
Messages
2 096 134
Membres
106 504
dernier inscrit
Kannankin Maxime