Fonction SOMMEPROD en fonction d'un critère

Bonjour le forum,

J'ai actuellement une formule fonctionnel qui me permet de faire la somme de critère en fonction d'un base de donnée nommé, et je suis actuellement coincé sur le fait que je voudrais que ma formule ne s'applique que sur une certaine période (mois).

J'aurais donc besoin que m'a formule aille chercher dans le tableau2[mois] toutes les dates de qui correspondent au chiffre du mois (janvier = 1; février = 2, etc..) et ensuite reprendre la formule initiale pour le calcule qui est par exemple :

SOMMEPROD(VERSEMENT*(NB.SI(INDIRECT(I29);OPERATION)>0))

Je vous laisse ci-joint un fichier afin d'y voir plus clair, tout se trouve sur la feuille "TEST 1"

Merci d'avance pour votre aide !

9test2.xlsm (39.93 Ko)

Bonjour,

Comme ceci peut être :

=SOMMEPROD((Tableau2[mois]=H24)*1;Tableau2[VERSEMENT])

Bonjour et merci de ta réponse Theze,

J'aimerai en effet que la formule tienne compte de ce critère tout en calculant de la même façon que ma formule de base

L'utilisation de Sommeprod() requiert que toutes les plages utilisées en argument soient d'un nombre égal de cellules ce qui n'est pas le cas ici donc, ajoutes une colonne à ton tableau avec une liste de validation ayant comme source ta zone "OPERATION" et définis l'opération pour chaque ligne

Theze,

Effectivement, en passant par ta solution je me complique moins la vie.

Maintenant j'ai juste un autre question, afin de pouvoir utiliser la sommeprod sur mon tableau 2, il faut que j'alimente le tableau de selection de donnée et ensuite ma base de donnée en fonction de mes catégories par le biais d'une macro.

Le but est que après avoir créer une nouvelle ligne j'aimerai que si xxx fait partie de la categorie "AIDE" alors selectionner la plage complète et coller sur le tableau des catégories :

Sub COPIE_AIDE()
'
' COPIE_AIDE Macro
'

    ActiveSheet.ListObjects("LISTE").Range.AutoFilter Field:=2, Criteria1:= _
    "AIDE"

    Range("LISTE[Description]").Select
    Selection.Copy
    Sheets("CATEGORIE").Select
    Range("A2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
End Sub

Mais le problème c'est que

Range("LISTE[Description]").Select

me sélectionne la colonne complète tandis que moi je veux seulement copier, après tri sélectif du tableau dynamique la ligne qui détienne "aide" dans la colonne CATEGORIE.

Je t'envoi ma MAJ du fichier

6test2.xlsm (93.98 Ko)

Bonjour,

Le problème c'est que dans le classeur posté, il n'exista pas de tableau "LISTE" et je n'arrive pas à savoir lequel pourrai l'être car il n'y en a aucun que possède un champ "AIDE" en seconde position :

ActiveSheet.ListObjects("LISTE").Range.AutoFilter Field:=2, Criteria1:="AIDE"

Si c'est le tableau dans la feuille "LINSTING" qui comporte les colonnes "description";"zone catégorie";"anticipation débit", etc..

et au moment ou je fais mon tri, je veux pouvoir sélectionner toutes les cellules de "description" une fois que "zone catégorie" à était trié en fonction de mon critère "aide","courses" etc..

c'est la que la macro ne fonctionne pas en fonction du tableau.

Sub COPIE_AIDE()

'

' COPIE_AIDE Macro

'

ActiveSheet.ListObjects("LISTE").Range.AutoFilter Field:=2, Criteria1:= _

"AIDE"

Range("LISTE[Description]").Select

Selection.Copy

Sheets("CATEGORIE").Select

Range("A2").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

End Sub

6test2.xlsm (98.47 Ko)

Une piste avec contournement de l'objet ListObjects("LISTE").

Le résultat du filtrage est pour le test collé à partir de la cellule A13 et la ligne 13 est ensuite supprimée car le filtre embarque la ligne d'entêtes !

Testes et adaptes :

Sub CopieFiltre()

    Dim Plage As Range

    With Worksheets("LISTING")

        Set Plage = .Range(.Cells(21, 1), .Cells(Rows.Count, 5).End(xlUp)) 'le tableau "LISTE"

        Plage.AutoFilter 2, "=AIDE"

        'ici la copie du résultat à partir de A21 pour le test
        .AutoFilter.Range.EntireRow.Copy Worksheets("CATEGORIE").Cells(13, 1)

        'comme dans la copie d'un filtre la ligne d'entête est automatiquement embarquée, suppression
        Worksheets("CATEGORIE").Rows(13).Delete

        Plage.AutoFilter

    End With

End Sub

Merci pour ta réponse, désolé je dois encore t'embêter mais lors du lancement de la macro, le tri fonctionne et j'ai ensuite l'erreur :

"Erreur d'exécution 91 :

Variable objet ou variable de bloc With non définie"

Et m'affiche cette ligne :

.AutoFilter.Range.EntireRow.Copy Worksheets("CATEGORIE").Cells(13, 1)

Je viens de faire les test et effectivement au premier lancement de la procédure j'ai cette même erreur mais ceci vient de la présence d'un "résidu" de filtre élaboré !!!

Voici le code qui le corrige mais si tu cliques dans le tableau puis sur l'onglet "Données" et le petit bouton "Effacer" et pour finir, sur le bouton "Filtrer", le tableau sera nettoyer de ce résidu de filtre et mon code fonctionnera :

Sub CopieFiltre()

    Dim Plage As Range

    With Worksheets("LISTING")

        .ListObjects("LISTE").Sort.SortFields.Clear 'à supprimer après la première exécution
        .Range("A22").Select 'à supprimer après la première exécution
        Selection.AutoFilter 'à supprimer après la première exécution

        Set Plage = .Range(.Cells(21, 1), .Cells(Rows.Count, 5).End(xlUp)) 'le tableau "LISTE"

        Plage.AutoFilter 2, "=AIDE"

        'ici la copie du résultat à partir de A21 pour le test
        .AutoFilter.Range.EntireRow.Copy Worksheets("CATEGORIE").Cells(13, 1)

        'comme dans la copie d'un filtre la ligne d'entête est automatiquement embarquée, suppression
        Worksheets("CATEGORIE").Rows(13).Delete

        Plage.AutoFilter

    End With

End Sub

Après insertion de la macro que tu viens de m'envoyer, j'ai l'erreur numéro : "1004" : La méthode select de la classe Range à échoué ?

bonjour,

As-tu essayé ceci :

si tu cliques dans le tableau puis sur l'onglet "Données" et le petit bouton "Effacer" et pour finir, sur le bouton "Filtrer", le tableau sera nettoyer de ce résidu de filtre et mon code fonctionnera

avant de faire tourner le code sans les lignes ci-dessous ?

.ListObjects("LISTE").Sort.SortFields.Clear 'à supprimer après la première exécution
.Range("A22").Select 'à supprimer après la première exécution
Selection.AutoFilter 'à supprimer après la première exécution

Oui, ceci à fonctionné la première fois.

Mais quand la copie à fonctionné, elle à copié toutes les colonnes au lieu seulement de la colonne A.

Depuis je n'ai plus rien a effacé, et j'ai ce code d'erreur.

Si tu ne veux copier que la première colonne, il faut le lui dire, tu remplaces :

.AutoFilter.Range.EntireRow.Copy Worksheets("CATEGORIE").Cells(13, 1)

par :

Plage.Columns("A:A").Cells.SpecialCells(xlCellTypeVisible).Copy Worksheets("CATEGORIE").Cells(13, 1)

voici le code :

Sub CopieFiltre()

    Dim Plage As Range

    With Worksheets("LISTING")

        Set Plage = .Range(.Cells(21, 1), .Cells(Rows.Count, 5).End(xlUp)) 'le tableau "LISTE"

        Plage.AutoFilter 2, "=AIDE"

        Plage.Columns("A:A").Cells.SpecialCells(xlCellTypeVisible).Copy Worksheets("CATEGORIE").Cells(13, 1)

        'comme dans la copie d'un filtre la ligne d'entête est automatiquement embarquée, suppression
        Worksheets("CATEGORIE").Rows(13).Delete

        Plage.AutoFilter

    End With

End Sub

Super ! la formule fonctionne du feux de dieux!

Je te remercie pour ton aide, un grand MERCI à toi

Content de t'avoir aidé

Je viens juste de penser à quelque chose, si je veux dupliquer la macro de façon à faire ce process sur tout les critères (AIDE, COURSE, etc..) sans pour autant devoir faire une macro pour chaque, c'est possible ?

J'ai essayé seul de copier la formule dans une seule macro mais cela n'a pas donné le résultat souhaité

Bonjour,

Non, tu peux avoir une multitude de façons pour indiquer le critère comme par exemple ce que je te propose ici. Quand tu lances la procédure, une boite de dialogue te demande de sélectionner une des cellules contenant le critère voulu, tu sélectionnes une cellule puis OK et le filtre est exécuté, si pas de sélection ou sélection de plus d'une cellule, message et fin de procédure :

Sub CopieFiltre()

    Dim Plage As Range
    Dim Cel As Range

    With Worksheets("LISTING")

        On Error GoTo Fin

        Set Cel = Application.InputBox("Sélectionnez une des cellules contenant le critère !", , , , , , , 8)
        If Cel.Count > 1 Then Err.Raise vbObjectError + 513

        On Error GoTo 0

        Set Plage = .Range(.Cells(21, 1), .Cells(Rows.Count, 5).End(xlUp)) 'le tableau "LISTE"

        Plage.AutoFilter 2, "=" & Cel.Value

        Plage.Columns("A:A").Cells.SpecialCells(xlCellTypeVisible).Copy Worksheets("CATEGORIE").Cells(13, 1)

        'comme dans la copie d'un filtre la ligne d'entête est automatiquement embarquée, suppression
        Worksheets("CATEGORIE").Rows(13).Delete

        Plage.AutoFilter

    End With

    Exit Sub

Fin: MsgBox "Abandon ou sélection de plus d'une cellule !"

End Sub

Bonjour Theze,

Je te remercie pour ton aide, j'avais créer un autre poste pour une erreur sur ta macro l'on m'a trouvé une autre solutions :

https://forum.excel-pratique.com/viewtopic.php?p=750560#p750560

Je la trouve un peu plus intéressante et pratique mais je note tout de même la tienne, cela peut toujours servir !

Merci encore !

Rechercher des sujets similaires à "fonction sommeprod critere"