Totaliser valeurs communes répétées de 2 colonnes

[philips9]

Bonjour,

Je recherche une formule pour totaliser des valeurs communes à 2 colonnes qui se répètent . Je sais totaliser les valeurs communes de 2 colonnes (fichier joint). Mais je n'arrive pas à totaliser exclusivement celles qui se répètent.

Je ne sais pas si au fond Excel peut le faire par une formule mais j’ai l’impression que c’est possible. Une sommeprod lier à une Frequence ou NB.SI? Malgré la lecture de nombreux posts je n'y arrive pas.

Voilà, d’avance merci pour vos réponses.

Bien cordialement,

 

[Robert]

Re : Totaliser valeurs communes répétées de 2 colonnes

Bonjour Philips, bonjour le forum,

Une solution macro pourrait-elle te convenir ?

Sub Macro1()
Dim cel As Range 'déclare la variable cel (CELlule)
Dim v As Variant 'déclare la variable v (Valeur)
Dim n As Integer 'déclare la variable n (Nombre)
 
With Sheets("sommeproddoublonsrépétés") 'prend en compte l'onglet "sommeproddoublonsrépétés"
    For Each cel In .Range("A1:A" & .Range("A65536").End(xlUp).Row) 'boucle sur toutes les cellules éditées cel de la colonne A
        v = cel.Value 'définit la valeur v
        'si cel+1 (colonne B), 1+cel (une ligne en dessous), 1+cel+1 (une ligne en dessous, colonne B) = v, incrémente le nombre n
        If cel.Offset(0, 1).Value = v And cel.Offset(1, 0).Value = v And cel.Offset(1, 1).Value = v Then n = n + 1
    Next cel 'prochaine cellule de la boucle
    MsgBox n 'message affichant n
End With 'fin de la prise en compte de l'onglet "sommeproddoublonsrépétés"
End Sub

 

[JNP]

Re : Totaliser valeurs communes répétées de 2 colonnes

Bonjour Philips9 et bienvenue, bonjour Robert 🙂,
Pas bien sûr d'avoir compris ton problème...

=SOMMEPROD((A1:A20=C1)*(B1:B20=A1:A20))-1

donne le résultat souhaité (inutile de le valider en matriciel, SOMMEPROD est déjà matricielle 😉).
Bonne journée 😎

 

[philips9]

Re : Totaliser valeurs communes répétées de 2 colonnes

Bonjour Robert, bonjour JNP,

Avant tout merci pour vos réponses.

Robert, la solution macro je ne connais pas du tout. Si tu pouvais la convertir en formule, je te serai très reconnaissant.
JNP, j'ai appliqué ta formule elle me retourne comme résultat -1. Sauf erreur de ma part, il manque quelque chose.
Bien cordialement,

Jeff en direct de l'Inde.

 

[JNP]

Re : Totaliser valeurs communes répétées de 2 colonnes

Re 🙂,
Ton fichier modifié en retour.
Bonne journée 😎

 

[david84]

Re : Totaliser valeurs communes répétées de 2 colonnes

Bonjour, JNP😉, Rbobert😉
pour éviter le"-1" si le résultat est égale à 0 :

=SI(SOMMEPROD((A1:A20=C1)*(B1:B20=C1))=0;0;SOMMEPROD((A1:A20=C1)*(B1:B20=C1))-1)

A+

 

[philips9]

Re : Totaliser valeurs communes répétées de 2 colonnes

Bonjour David84, Robert, JNP,

Je vous renouvelle mes remerciements pour vos solutions. Mea culpa Robert pour avoir manqué d'attention à la lecture de ton premier post et pour avoir été trop simpliste dans ma demande. En effet, si vos formules sont la solution au problème, toutefois, lorsqu'elle rencontre une paire 5-5 qui ne se répète pas, elle la comptabilise alors que ce n'est pas ce que je souhaite. J'ai bien essayé de résoudre le problème en me triturant les méninges, mais rien n'y fait. De plus j'ai promis à des enfants indiens de les emmener faire du foot, et ils piaffent d'impatience. Chose promise chose due (ici il est déjà 17h15). Merci d'avance pour votre contribution. Ci-joint nouveau fichier.
Bien cordialement,

Philips9 en direct de l'Inde ou quand il pleut pas, il fait très chaud...

 

[JNP]

Re : Totaliser valeurs communes répétées de 2 colonnes

Re 🙂,
Effectivement, tu peux faire un mea culpa à Robert, car lui avait tout compris 😉.
J'ai repris sa macro pour la transformer en fonction personnalisée. Vois si ça te convient (une fois le foot fini 😀).
Bonne fin de journée 😎

 

[philips9]

Re : Totaliser valeurs communes répétées de 2 colonnes

Bonsoir JNP,

En fait le mea culpa s'adressait à toi quand je te disais qu'il manquait quelque chose dans ta formule. Les enfants trépignant autour de moi, je me suis trompé. Merci, la fonction est parfaite. Mais puisque tu me laisses le choix, je t'avoue que je préfèrerais un sommeprod facile à copier coller, mais qui me permettrait aussi de comprendre le mécanisme. La fonction est une première pour moi. En tous cas, un gros merci pour ce que tu as fait et aussi à Robert & David 84. C'est super.

Bonne soirée,

Jeff en direct de l'Inde .... les enfants étaient très heureux du foot.

 

[JNP]

Re : Totaliser valeurs communes répétées de 2 colonnes

Re 🙂,

Mais puisque tu me laisses le choix, je t'avoue que je préfèrerais un sommeprod facile à copier coller, mais qui me permettrait aussi de comprendre le mécanisme. La fonction est une première pour moi.

J'ai écrit ça, moi 😛 ?
Désolé, mais même si je fais des progrès en formules, je suis avant tout VBA 😀.
Pour réussir ça en formule, il faudrait que des "grosses pointures" viennent s'y frotter 🙄. Qui sait, avec un peu de chance 😉.
Bon courage 😎

 

[philips9]

Re : Totaliser valeurs communes répétées de 2 colonnes

Bonsoir JNP,

Sinon comment insérer ta fonction à divers endroits de ma feuille de calcul, par simple copier coller?

Merci d'avance et bonne soirée.

Philips9

 

[JNP]

Re : Totaliser valeurs communes répétées de 2 colonnes

Re 🙂,
C'est plutôt la fonction de Robert, je n'ai fait que passer de la Sub en Function en récupérant son code 🙄...
La fonction est utilisable comme toutes les autres formules disponibles sous Excel. Tu écris

=DoublonsRépétés([COLOR=red]la plage que tu souhaites vérifier[/COLOR];[COLOR=red]la valeur qui t'intéresse[/COLOR])

Seules obligations, la plage doit être rectangulaire sur 2 colonnes et la valeur un entier.
Si tu veux renvoyer uniquement le nombre dans une cellule et les lignes dans une autre, ou tester des valeurs décimales, il suffit juste de faire quelques transformations 😛.
Bon courage 😎

 

[Tibo]

Re : Totaliser valeurs communes répétées de 2 colonnes

Bonjour à tous, salut Jean Noël 🙄,

Bon, pas sûr de tout avoir compris.

Un début de piste :

Une formule matricielle qui permet de déterminer l'emplacement du premier "carré" de 5 (ou de toute autre valeur cherchée) :

=EQUIV(1;($A$1:$A$960=$B$1:$B$960)*($A$1:$A$960=$C$1)*(($A$2:$A$961=$B$2:$B$961)
*($A$2:$A$961=$C$1));0)+1

Matricielle à valider par CTRL + MAJ + ENTREE

Le tout, sous réserve d'avoir compris ce qu'il fallait chercher

@+

 

[JNP]

Re : Totaliser valeurs communes répétées de 2 colonnes

Re 🙂, salut Tibo (et merci 😉),
Pas mal, ça renvoie la première ligne, ce qui serait bien, c'est d'avoir les autres en dessous 😛.
Mais surtout, c'est le nombre de carrés de 5 qui intéresse notre ami. Aurais-tu une idée 😉 ?
A + 😎

 

[JNP]

Re : Totaliser valeurs communes répétées de 2 colonnes

Re 🙂,
En essayant de comprendre (c'est pas gagné 😀) la formule de Tibo, j'ai réussi à l'adapter pour les carrés 1, 2 et 3 en formules nommées 😱.
Un exploit pour moi 😛...
En pièce jointe, le résultat, il suffirait de prolonger avec Test4, etc. suivant les besoins 🙄...
Bonne soirée 😎