(Résolu) SUMPRODUCT avec condition (pour éviter les doublons) en VBA

[romrom]

Bonjour le Forum,

Je viens vers vous avec une question que je ne pense pas trop compliquée. C'est quelque chose que j'avais déjà réussi à faire, mais incapable de réitérer, ni même trouver où sur internet j'avais trouvé la solution. Je m'explique :
J'ai une base de données (BDD) dans laquelle je répertorie, entre autres, des salons. Pour chacun de ces salons, j'ai autant de lignes que d'éléments à produire (en vue de ces différents salons). J'aimerais connaître le nombre de salon dans ma base (colonne C), marqué "en cours" (colonne L), mais en faisant un filtre sur la colonne NOM (colonne B) afin de ne pas comptabiliser plusieurs fois un même salon.

J'ai une formule du type :
f = ActiveSheet.UsedRange.Rows.Count
Application.Evaluate("=SUMPRODUCT((BDD!C2:C" & f & "=""Salon"")*(BDD!L2:L" & f & "=""En-cours"")*1)")
Celle-ci marche, mais me renvoie le nombre totale d'occurences de "salon" ET "En-cours". J'aimerai intégrer un trie sur la donnée Nom (Colonne L). Je crois qu'il faut intégrer la commande 1/COUNTIF mais je ne me souvient plus de la syntaxe.... Si quelqu'un sait m'aider ça serait génial.
EN vous remerciant

 

[job75]

Re : SUMPRODUCT avec condition (pour éviter les doublons) en VBA

Bonjour romrom,

Puisque vous utilisez VBA cette solution est sûrement préférable :

Option Compare Text 'la casse n'est pas prise en compte

Sub Compte()
Dim tablo, d As Object, i As Long, Nsalon As Long
tablo = Sheets("BDD").Range("A1:L1", Sheets("BDD").UsedRange)
Set d = CreateObject("Scripting.Dictionary")
For i = 1 To UBound(tablo)
  If tablo(i, 3) = "Salon" And tablo(i, 12) = "En-cours" _
    Then d(LCase(tablo(i, 2))) = ""
Next
Nsalon = d.Count
MsgBox Nsalon 'une manière d'afficher
End Sub

A placer où vous voulez.

Edit : j'ai ajouté LCase car Option Compare Text n'a pas d'effet sur l'objet Dictionary.

A+

 

[romrom]

Re : SUMPRODUCT avec condition (pour éviter les doublons) en VBA

Salut Job75, merci pour ta proposition.
Je l'ai copié collé dans mon classeur mais apparemment la ligne "Set d = CreateObject("Scripting.Dictionary")" ne passait pas... (Ce qui est peut être due aux différences PC/MAC, utilisant MAC personnellement). Je te remercie encore pour cette proposition mais j'aimerais vraiment trouver une solution avec SOMEPRODUCT (en VBA toujours) afin de rester cohérent avec le reste de mon projet (je pense à la pauvre personne qui peut être le reprendrais après moi), d'autant plus que je sais qu'elle marche je n'arrive juste plus à l'écrire correctement.

 

[job75]

Re : SUMPRODUCT avec condition (pour éviter les doublons) en VBA

Bonjour romrom, le forum,

Je suis étonné que l'utilisation du Dictionary ne soit pas possible sur MAC.

Alors voici 2 solutions évaluant des formules Excel :

Sub Compte1()
Dim f As Long, n As Long
f = Sheets("BDD").[C65536].End(xlUp).Row 'f = Evaluate("MATCH(""zzz"",BDD!C:C)")
n = Evaluate("SUMPRODUCT(N(ISNUMBER(LN(MATCH(BDD!B1:B" & f & "&""SalonEn-cours"",BDD!B1:B" & f & "&BDD!C1:C" & f & "&L1:L" & f & ",0)=ROW(1:" & f & ")))))")
MsgBox n 'une manière d'afficher
End Sub

Sub Compte2()
Dim f As Long, n As Long
f = Sheets("BDD").[C65536].End(xlUp).Row 'f = Evaluate("MATCH(""zzz"",BDD!C:C)")
n = Evaluate("COUNT(LN(MATCH(BDD!B1:B" & f & "&""SalonEn-cours"",BDD!B1:B" & f & "&BDD!C1:C" & f & "&L1:L" & f & ",0)=ROW(1:" & f & ")))")
MsgBox n 'une manière d'afficher
End Sub

Nota 1 : dans une feuille de calcul la 2ème formule nécessiterait une validation matricielle.

Nota 2 : on utilise MATCH (EQUIV) et pas COUNTIF (NB.SI) car la recherche se fait sur une matrice, pas sur une plage.

Nota 3 : si sur une ligne on a B vide, C = "Salon", L = "En-cours" cette suite sera comptabilisée.

A+

 

[romrom]

Re : SUMPRODUCT avec condition (pour éviter les doublons) en VBA

Encore merci Job75, je vais voir ce que je peux récupérer dans ce que tu m'as proposé. Je crois pas que l'on se soit tout à fait compris quant à ce que j'avais besoin (ce qui vient surement du fait que je me suis mal expliqué), ma colonne comportant le nom des salons ne comporte aucune cellule vide, je ne pouvais donc pas appliqué ta technique.

Néanmoins à force d'abnégation, j'ai retrouvé la solution (énervante de simplicité au final, au vus du temps que j'ai passé à la chercher) :


f = Sheets("BDD").UsedRange.Rows.Count

MsgBox Application.Evaluate("=SUMPRODUCT(1/countif(BDD!A2:A" & f & ",BDD!A2:A" & f & ")*(BDD!C2:C" & f & "=""Salon"")*(BDD!L2:L" & f & "=""En-cours"")*1)")
Ce qui me permet d'avoir le nombre de noms de salon différents dans la colonne A, mais ayant pour condition les étiquettes "Salon" en C et "En-cours" en L. Le filtre "anti doublon" étant appliqué sur la colonne A grâce à 1/countif().

Salut le forum