Somme conditionnelle sur plusieurs colonnes

[Sparf]

Bonjour à tous et à toutes !

Après plusieurs heures de recherche à travers le forum et internet, après des dizaines de tentatives avec différentes formules, je suis toujours au pied du mur et demande donc votre aide....

J'ai une base de donnée assez conséquente, plusieurs milliers de lignes, dans laquelle on trouve différente valeurs de type date, texte, horaire.
Les données types horaires sont réparties dans plusieurs colonnes en fonction de leurs correspondance, et ce que je recherche, c'est la somme de ces horaires suivant un ou plusieurs critères.

J'ai essayé avec les fonctions suivantes SOMME.SI, SOMMEPROD et les formules types matricielles SOMME(SI()). J'obtiens bien des résultats (cf fichier joint) mais aucun ne répond exactement à ce que je recherche.

Pour plus de clarté, j'ai tout ré-expliqué et détaillé dans l'exemple, et vous remercie d'avance pour votre aide.

 

[hoerwind]

Re : Somme conditionnelle sur plusieurs colonnes

Bonjour,

La fonction SOMMEPROD n'aime pas du texte dans les plages de recherche.
Supprime donc la ligne de titres au milieu du tableau.

Ensuite, essaie ceci :
=SOMMEPROD((B5:B14<AUJOURDHUI())*(B5:B14>MOIS.DECALER(AUJOURDHUI();-3))*E5:K14)

 

[Sparf]

Re : Somme conditionnelle sur plusieurs colonnes

Merci d'avoir répondu rapidement,

Mais cela ne résout pas mon problème, j'avais bien remarqué que la fonction SOMMEPROD n'aimait pas le texte, et comme je l'ai précisé sur l'exemple, je ne peux pas supprimer cette ligne de titre, pour des raisons de mise en page en cas d'impression. Cette ligne est d'ailleurs répétée des dizaines de fois tout au long du tableau.

Tout comme le fait que je ne peux définir en nombre de ligne une plage de donnée, vu que des nouvelles lignes sont régulièrement ajoutées à la BDD.

Le fichier joint n'est qu'un extrait de la BDD complète, il faut considéré qu'elle n'a pas de "fin" en hauteur, d'où la gestion par colonne. Pour l'exemple j'ai dû terminé mes colonnes à cause des explications, mais ce n'est pas possible sur l'original.

Merci quand même

 

[Tibo]

Re : Somme conditionnelle sur plusieurs colonnes

Bonjour,

Une tentative avec une formule matricielle en K30 :

=SOMME(SI(ESTNUM(Date1);SI(Date1>=MOIS.DECALER(AUJOURDHUI();-3);HeureFaite1)))

Formule matricielle à valider par CTRL + MAJ + ENTREE

Je te laisse tester

@+

 

[pierrejean]

Re : Somme conditionnelle sur plusieurs colonnes

Bonjour Sparf

Avec une fonction personnalisée

Voir cellules M4 et N4
Ne pas hesiter a revenir si tu as des questions

Edit: Salut Tibo

 

[hoerwind]

Re : Somme conditionnelle sur plusieurs colonnes

Re, salut les autres,

Il n'est pas nécessaire de répéter les en-têtes de colonnes pour les avoir sur toutes les pages lors de l'impression.
Le menu Fichier - Mise en page - Feuille - Lignes à répéter en haut
le fait automatiquement.

Définir un nombre de lignes en nommant une plage de façon dynamique évite de devoir modifier les références dans une formule à chaque ajout de données.

 

[pierrejean]

Re : Somme conditionnelle sur plusieurs colonnes

Re

Toutes mes excuses a hoerwind que j'ai oublié de saluer

 

[Sparf]

Re : Somme conditionnelle sur plusieurs colonnes

@ Tibo : J'avais pensé à une formule matricielle, mais celle-ci, bien qu'elle fonctionne parfaitement, est difficilement adaptable à ma BDD au vue de sa taille. Le calcul prend alors plusieurs dizaine de secondes et rend pénible toutes nouvelles saisies....

@ PierreJean : j'avais tenté d'essayé d'éviter le VBA pour garder un classeur de calcul simple, sans macro, mais il me semble dans le cas présent si je décide de garder ma BDD dans l'état actuel, je n'ai pas le choix.

@ Hoerwind : Merci bien pour cette info, je n'étais pas du tout au courant de cette possibilité, je vais travailler ce point, ce qui me permettrait d'utiliser la fonction SOMMEPROD

Sinon, bien que vos solutions soient difficilement adaptable à la taille de ma BDD, cela m'a aidé à penser différemment la structure de celle-ci. Et cela m'a amené à trouvé une petite astuce toute bête sans changer la mise en page de mon tableau, mais simplement en rajoutant une ènième colonne en extrémité droite de celui-ci.
En fait comme je ne peux saisir qu'une donnée horaire par ligne, j'ai juste eu à créer une nouvelle colonne, reportant la somme des horaire d'une ligne, ce qui me permet ensuite d'utiliser n'importe quelle fonction SOMME.SI, SOMMEPROD, ... vu que je n'ai plus qu'une seule colonne à gérer.
Cette astuce me permet aussi de garder la structure originelle de la BDD et donc de faire d'autre tris en fonction d'autre paramètre.
Pour plus de clarté à la mise en page, j'ai "caché" cette colonne en lui attribuant une largueur nulle.

Un grand merci à tous pour le temps que vous avez passé pour m'aider
Quand je pense que j'ai passé quelques semaines à chercher une solution et qu'elle était si simple.... (même si cela peut faire un peu "bricolage") ^^

 

[Sparf]

Re : Somme conditionnelle sur plusieurs colonnes

J'ai oublier de joindre l'exemple excel à mon message précédent, histoire d'illustrer l'astuce...

(désolé pour le double post)