Microsoft 365 Probleme SOMMEPROD pour sommer plusieurs colonnes selon plusieurs conditions

[Littlewink]

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,

 

[Dudu2]

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) ??

 

[Dudu2]

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))

 

[Littlewink]

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) ??

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.

 

[Littlewink]

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))

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

 

[Dudu2]

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))

 

[Littlewink]

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.

 

[Dudu2]

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.

 

[Littlewink]

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 ?

 

[Dudu2]

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 ?

 

[Littlewink]

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