Ceci est une page optimisée pour les mobiles. Cliquez sur ce texte pour afficher la vraie page.

Microsoft 365 Probleme SOMMEPROD pour sommer plusieurs colonnes selon plusieurs conditions

Créer une discussion
  • Initiateur de la discussion Initiateur de la discussion Littlewink
  • Date de début Date de début

Boostez vos compétences Excel avec notre communauté !

Rejoignez Excel Downloads, le rendez-vous des passionnés où l'entraide fait la force. Apprenez, échangez, progressez – et tout ça gratuitement ! 👉 Inscrivez-vous maintenant !

S'inscrire
L

Littlewink

XLDnaute Nouveau
Bonjour,
Je galère depuis de nombreuses heures pour effectuer un calcul automatique dans mon tableau de synthèse (travaux de bâtiments). Pour le moment il n'y a que 4 bâtiment mais au final ce tableau comportera environ 60 bâtiments.
C'est un tableau qui permet de chiffrer la maintenance en fonction des durées de vie des équipements.
L'objectif est de calculer automatiquement les coûts des travaux par catégorie de travaux et par année dans le tableau en dessous.
Et ensuite répercuter les montant totalisés de tous les bâtiment pour une année précise et par type de travaux.
La seule fonction qui fonctionne est SOMME.SI.ENS mais impossible à mettre en œuvre (humainement) avec les 3 données : 60 bâtiments/années et catégories de travaux.
Cela donnerai des formules à rallonges impossible à saisir pour chaque cellule.
Je n'ai pas réussi à comprendre le fonctionnement de la fonction DECALER qui peut-être pourrait fonctionner dans mon cas.

Je vous joins mon fichier, tout se passe dans le 2ème onglet Prog Tvx détail

Merci pour votre soutient !

Cordialement,
 

Pièces jointes

Trier par date Trier par votes
Bonjour,
Dans ta formule:
=SOMMEPROD(('Prog Tvx détail'!B5:B47=$B$4)*('Prog Tvx détail'!I5:I47=2020)*('Prog Tvx détail'!J5:J47)*('Prog Tvx détail'!M5:M47))
Que représent la colonne J * colonne M (un cout * cout) ??
 
Vote positif 0 Vote négatif
Si c'était pour le Parc Feuri uniquement, voici la formule avec le colonne J qu'il faudrait mettre en B5 et copier partout:
=SOMMEPROD(('Prog Tvx détail'!$B$5:$B$47=B$4)*('Prog Tvx détail'!$I$5:$I$47=$A5)*('Prog Tvx détail'!$J$5:$J$47))

L'inclusion dans la formule de la colonne M pose 2 problèmes:
1 - Ça n'a pas de sens de multiplier un coût par un coût
2 - La colonne M a des cellules vides et ça SOMMEPROD il n'aime pas du tout (#VALEUR!")
Edit: Correction !
2 - La colonne M a des cellules avec des chaines vides (issues de la formule) et ça SOMMEPROD il n'aime pas du tout pour faire la multiplication (#VALEUR!").
Il ne faut pas confondre une cellule vide que SOMMEPROD va considérer comme à 0 et une cellule contenant une chaine vide qui va le faire passer en #VALEUR!.
Hélas, le mot-clé Excel tellement nécessaire pour vraiment vider une cellule n'existe pas !
Et ça se comprend car si on vide la cellule, on efface la formule 😱 !

Je suppose que tu veux faire la somme des 2 couts (Parc Feuri & Espace Saint Ex) ?
Dans ce case, il faut:
1 - Modifier la formule en colonne M pour mettre 0 là où il n'y a pas de valeur.
2 - Faire la somme des 2 SOMMEPROD (en B5 et étendre partout)
=SOMMEPROD(('Prog Tvx détail'!$B$5:$B$47=B$4)*('Prog Tvx détail'!$I$5:$I$47=$A5)*('Prog Tvx détail'!$J$5:$J$47))
+SOMMEPROD(('Prog Tvx détail'!$B$5:$B$47=B$4)*('Prog Tvx détail'!$I$5:$I$47=$A5)*('Prog Tvx détail'!$M$5:$M$47))
 
Dernière édition:
Vote positif 0 Vote négatif
Dudu2 à dit:
Bonjour,
Dans ta formule:
=SOMMEPROD(('Prog Tvx détail'!B5:B47=$B$4)*('Prog Tvx détail'!I5:I47=2020)*('Prog Tvx détail'!J5:J47)*('Prog Tvx détail'!M5:M47))
Que représent la colonne J * colonne M (un cout * cout) ??
Cliquez pour agrandir...

Bonjour Dudu2,

Déjà merci de te pencher sur mon problème 🙂
La colonne J représente un coût forfaitaire pour ce type de travaux.
La colonne M c'est idem mais pour un autre bâtiment. L'objectif est d'avoir tous mes bâtiments à la suite ainsi.

Je cherche justement avoir l'addition des résultats de tous les bâtiments pour chaque cellules du tableau de synthèse.
 
Vote positif 0 Vote négatif


Cool, on progresse dans la réflexion. Et effectivement procédons pas étape.
Alors effectivement, j'avais lu que SOMMEPROD n'aime pas les vides. Dans ce cas, dans les colonnes J, M, P, etc... plutôt que de mettre vide si pas de date de prochain travaux, je peux lui indiquer de mettre 0 ?
Visiblement oui, c'est ce que tu indique en 1-
Est-ce que cela va résoudre le problème de #VALEUR! ? je vais faire le test.

Dans ton 2ème paragraphe, oui l'objectif est de faire l'addition des 2 coûts avec tous les bâtiments en fonction des dates (de prochain travaux et type de travaux).
Tu es donc passer dans le 3ème onglet. OK

Par contre imaginons que cela fonctionne (je te ferai un retour du test après modif), comment faire appliquer cette formule lorsque le tableau comptera 60 bâtiments ?

Merci pour tes conseils et le temps que tu y passes

Cdt
 
Vote positif 0 Vote négatif
Ok donc ce ne sont pas 2 bâtiments que tu veux additionner mais 4 ?
Le principe reste le même:
1 - Mettre à 0 au lieu de "" dans les formules des colonnes J, M, P, S les valeurs vides des colonnes de Coût.
2 - Ajouter les SOMMEPROD les uns aux autres (en B5 et copier partout dans Synthèse)
=SOMMEPROD(('Prog Tvx détail'!$B$5:$B$47=B$4)*('Prog Tvx détail'!$I$5:$I$47=$A5)*('Prog Tvx détail'!$J$5:$J$47))
+SOMMEPROD(('Prog Tvx détail'!$B$5:$B$47=B$4)*('Prog Tvx détail'!$I$5:$I$47=$A5)*('Prog Tvx détail'!$M$5:$M$47))
+SOMMEPROD(('Prog Tvx détail'!$B$5:$B$47=B$4)*('Prog Tvx détail'!$I$5:$I$47=$A5)*('Prog Tvx détail'!$P$5:$P$47))
+SOMMEPROD(('Prog Tvx détail'!$B$5:$B$47=B$4)*('Prog Tvx détail'!$I$5:$I$47=$A5)*('Prog Tvx détail'!$S$5:$S$47))
 

Pièces jointes

Vote positif 0 Vote négatif
Re salut Dudu2

J'ai fait le 1- et 2- et cela fonctionne pour ma premiere cellule B5. Super !
En revanche, lorsque je modifie les données dans mon tableau principal, le tableau de synthèse ne s'actualise pas automatiquement... Comment faire ?

Sinon, je craignais ta réponse pour les 60 bâtiments... N'existe-il pas une formule pour automatiser une recherche sur toutes les mêmes colonnes sachant que l'intervalle sera régulier ? A savoir un décalage de 3 colonnes.
 
Vote positif 0 Vote négatif
Si ton tableau va vers 60 bâtiments je pense qu'il sera difficile à gérer.
Il serait plus judicieux de faire des feuilles différentes pour chacune 10 bâtiments par exemple.
Ça restera gérable avec les SOMMEPROD manuels.
Sinon il faudra passer sur une Macro VBA car je ne vois pas une formule contenant une somme de 60 SOMMEPROD.

Les formules donnent le résultat selon les valeurs qu'elles utilisent.
Les types de travaux à comparer sont pris dans les Titres des colonnes de Synthèse en ligne 4.
S'ils ne sont pas orthographiés exactement comme ceux du tableau des chiffres, il ne trouvera pas.
De même pour les années qui sont prises dans dans la colonne A de Synthèse.
C'est le principe du SOMMEPROD.
 
Vote positif 0 Vote négatif
Malheureusement c'est bien ce que je pensais...
Donc si je comprends tu me suggère de créer 6 feuilles (onglets) comprenant chacune 10 bâtiments ? C'est bien ça ?
Dans ce cas je dois faire des tableau de synthèse intermédiaire avec la formule que tu m'a indiqué ?
Du coup je limite les formules à rallonge (à 10 bâtiments) mais ensuite comment je répercute le tout dans un tableau final de synthèse ? J'additionne simplement les résultat de chaque cellules de "sous synthèse" de chaque feuille ?
 
Vote positif 0 Vote négatif
Si les lignes et les colonnes sont identiques, en effet la synthèse globale est la sommes des synthèses.

Tu as réglé le problèmes des noms de colonnes pour qu'ils soient orthographiés comme les types de travaux de manière à ce que les comptes soient justes ?
 
Vote positif 0 Vote négatif
Non je n'ai pas fait la modif d'orthographe, mais je vais le faire.

Et oui je pense que je vais adopter ta technique de faire plusieurs feuilles avec "sous-synthèse" et synthèse globale en final.
En tout cas un grand merci à toi Dudu2 pour ta réactivité et tes propositions !

MERCI !

Cdt
 
Vote positif 0 Vote négatif
Identifiez-vous ou inscrivez-vous pour participer.
Rejoins nos membres "Supporter XLD" et booste ton expérience Excel !
- Navigue sans publicité
- Accède à Cléa, notre assistante IA experte Excel... et pas que...
- Profite de fonctionnalités exclusives
Ton soutien permet à Excel Downloads de rester 100% gratuit et de continuer à rassembler les passionnés d'Excel.
Je deviens Supporter XLD
Menu
Connexion
S'inscrire
Les cookies sont requis pour utiliser ce site. Vous devez les accepter pour continuer à utiliser le site. En savoir plus…