sommeprod est-il utilisable

[f69815]

bonjour le forum,

je pensais maîtriser la fonction sommeprod, mais dans le cas que je vous soummet elle ne me semble pas appropriée.

dans le fichier joint il y a 2 feuilles : 2010 et recap. par contre le classeur final aura des feuilles de 2010 à 2015.

la feuille 2010 contient les 12 mois de l'année en cours.
la feuille recap cumule par année et par catégorie les données.


je voulais utiliser sommeprod car certains mois, non connu à l'avance, je ne souhaite pas les cumuler dans la feuille recap. je comptais pour cela mettre une valeur "P" dans la cellule précèdent la cellule contenant "etudes" pour servir de filtre.

je pense que l'anomalie vient du fait que les données à additionner n'appartiennent pas à la même plage.

avez-vous une solution, si possible sans vb ?

merci d'avance et bonne journée.

 

[JCGL]

Re : sommeprod est-il utilisable

Bonjour à tous,

Peux-tu essayer en B2 de 'Recap' et vers le bas et à droite :

=SOMMEPROD((INDIRECT("'"&B$1&"'!$B$2:$CE$2")=$A2)*(INDIRECT("'"&B$1&"'!$B$36:$CE$36")<>"Etudes")*INDIRECT("'"&B$1&"'!$B$3:$CE$33"))

Si une cellule de la ligne 36 contient 'Etudes', les valeurs de la colonne ne sont pas prises en compte

A+ à tous

Edition : Salut David (mais ai je bien compris ?)

 

[david84]

Re : sommeprod est-il utilisable

Bonjour,
sous réserve d'avoir compris ta demande, un début de réponse à rentrer en B2 de la feuille recap
=SOMMEPROD(('2010'!$B$2:$CE$2=recap!$A2)*('2010'!$B$3:$CE$34)

A+

Bien vu JCGL, c'est en voyant ta réponse que j'ai compris ce que voulais réellement f69815

 

[phlaurent55]

Re : sommeprod est-il utilisable

Bonjour à tous,

une solution par macro en pièce jointe

à+
Philippe

 

[f69815]

Re : sommeprod est-il utilisable

merci à vous deux,

david84: ta formule fonctionne, mais comme tu l'as remarqué il manque la notion de filtre.

JCGL: le principe est bon et fonctionne. un petit problème tout de même, ce n'est pas une catégorie en particulier que je ne veux pas prendre en compte mais l'ensemble du mois, c'est pour ça que je précisais que je comptais mettre une valeur "P" dans la cellule précèdent la cellule contenant "etudes" pour servir de filtre et pour un mois complet.

est-ce clair ?

 

[f69815]

Re : sommeprod est-il utilisable

merci phlaurent55,

en vb je sais faire et ton code correspond exactement à ma demande.
merci à toi.

mais peut-être qu'en formule ce n'est pas possible.

 

[phlaurent55]

Re : sommeprod est-il utilisable

Re,

en vb je sais faire et ton code correspond exactement à ma demande.

pour éviter de cliquer sur le bouton que j'ai mis en bas de la feuille et pouvoir ainsi le supprimer, on peut passer par macro évènementielle
(Private Sub Worksheet_Change(ByVal Target As Range)
qui se déclencherait uniquement en cas de changement sur les 12 cellules concernées

.................. par formules , je n'y arrive pas 😡

à+
Philippe

 

[david84]

Re : sommeprod est-il utilisable

Re;
pas sûr d'avoir compris😕
Peux-tu nous mettre un exemple concret sur ta feuille (les résultats attendus par exemple), cela nous aiderait à mieux te comprendre...

 

[JCGL]

Re : sommeprod est-il utilisable

Bonjour à tous,

Un P dans une colonne "Mois" et le mois n'est pas pris en compte
Formule inchangée
Ajout d'une MeFC pour masquer les #REF si la feuille n'existe pas et les zéros

A+ à tous

 

[f69815]

Re : sommeprod est-il utilisable

re,
phlaurent55 : on peut également lancer la macro en activant la feuille "recap", cela évite les recalcul à chaque saisie.

david84 : fichier joint

 

[david84]

Re : sommeprod est-il utilisable

Re;
un essai avec une ligne (ligne 37) rajoutée dans le tableau.
Lorsque "Etudes" figure dans l'une des cellules de la ligne du dessus, "P" apparaît dans la ligne 37 et les valeurs ne sont pas prises en compte.
En effaçant "Etudes", "P" n'apparaît plus et les données sont prises en compte.
A tester

NB: pour 2011 et +, la formule affiche "pas trouvé" au lieu de #ref mais tu peux remplacer par ce que tu veux ou laisser la cellule vide).

 

[JCGL]

Re : sommeprod est-il utilisable

Bonjour à tous,

Dans mon dernier fichier déposé le #REF n'est pas visible, de même que les 0, par une MeFC, moins gourmande qu'un double SOMMEPROD()

A+ à tous

 

[f69815]

Re : sommeprod est-il utilisable

bonsoir à tous,
je ne vous avais pas oublié, mais un problème de santé m'a éloigné du clavier.

je viens seulement de reprendre et j'ai testé le fichier "jc test", jcgl.

celui-ci me convient parfaitement, je n'ai pas fait tous les tests possible avec les différentes années et possibilités.

donc à suivre !!!!

 

[f69815]

Re : sommeprod est-il utilisable

bonsoir à tous,

voila, j'ai terminé mon fichier et tout fonctionne très bien.
grand merci à jcgl et à tous les contributeurs.

à +