XL 2013 Formule SOMMEPROD

[Gilles64]

Bonjour à toutes et à tous.
Je suis actuellement en rain de réalisé un tableau simple pour mes compte bancaire, mais je n'arrive pas à résoudre ma formule qui est la suivante :

=SOMMEPROD((($Janvier.$C6:$C200=B2)*$Janvier.$E6:$E200)-($Janvier.$C6:$C200=B2)*$Janvier.$F6:$F200)

J'ai systématiquement une réponse #VALEUR!

La colonne C6:C200 correspond au libellé, la colonne E6:E200 correspond au débit et la colonne F6:F200 correspond au crédit qui se trouve sur une même feuille.
La cellule B2 est avec une liste de données, en résumé, je souhaite en fonction de ce que je met en B2 Excel m'additionne l'ensemble des débits (colonne E) moins les crédits (colonne F).

Merci par avance pour votre aide éclairée et votre bienveillance.

 

[DJunqueira]

Bonjour Gilles64

Configurez un exemple simple dans la feuille de calcul sans informations réelles.

 

[TooFatBoy]

Bonjour,

=SOMMEPROD((($Janvier.$C6:$C200=B2)*$Janvier.$E6:$E200)-($Janvier.$C6:$C200=B2)*$Janvier.$F6:$F200)

D'abord vu d'ici cette formule doit être simplifiable selon le principe mathématique du facteur commun, et ensuite il me semble que SOMMEPROD n'aime pas les cellules vides.

 

[Gilles64]

Bonjour DJunqueira,
Voici le fichier, la formule est sur la feuille Récapitulatif et recherche en B3, et l'idéale c'est de la recopier sur l'ensemble de ce tableau.
Merci.

 

[sylvanu]

Bonjour,
Dans ce cas précis, Somme.si est plus simple à utiliser :

=SOMME.SI(Janvier!C6:C200;B2;Janvier!F6:F200)-SOMME.SI(Janvier!C6:C200;B2;Janvier!E6:E200)

Fonction SOMME.SI - Support Microsoft

Comment utiliser la fonction SUMIF dans Excel pour ajouter les valeurs d’une plage qui répondent aux critères que vous spécifiez.

support.microsoft.com

 

[sylvanu]

Ou avec votre PJ. Avec en plus complexe :

=SOMME.SI(INDIRECT($A3&"!C6:C200");B$2;INDIRECT($A3&"!F6:F200"))-SOMME.SI(INDIRECT($A3&"!C6:C200");B$2;INDIRECT($A3&"!E6:E200"))

L'avantage en utilisant Indirect est que la formule est la même sur tout le tableau.
On prend l'item de la ligne 2 et le mois de la colonne A.

Fonction INDIRECT - Support Microsoft

Comment utiliser la fonction INDIRECT dans Excel pour modifier la référence à une cellule dans une formule sans modifier la formule elle-même.

support.microsoft.com

 

[DJunqueira]

Le problème avec votre formule est que la cellule E45 contient le texte "Débit", ce qui provoque une erreur de valeur.
Une solution possible.

=SOMMEPROD(SIERREUR((Janvier!$C6:$C200=B2)*Janvier!$E6:$E200;0)-SIERREUR((Janvier!$C6:$C200=B2)*Janvier!$F6:$F200;0))

 

[DJunqueira]

Quelques suggestions:
J'aime particulièrement les tables nommées, je pense que vous pourriez en profiter.
C'est une chose de stocker des données et une autre d'afficher des informations. Souvent, vous n'avez pas besoin d'autant d'onglets pour contrôler vos revenus et dépenses mensuels.

 

[Gilles64]

Merci pour vos retour.
j'ai donc trouver mon bonheur dans vos formule.
Ma chère et tendre moitié souhaitait un classeur qui ressemblais beaucoup à son cahier de compte quelle utilise, ce que femme veux ... Dieu le veux.

Encore merci pour votre aide.

 

[DJunqueira]

Je suis heureux que cela ait aidé, merci pour vos commentaires.

 

[job75]

Bonsoir à tous,

Sur Excel 2013 la formule du post #7 nécessite une validation matricielle par Ctrl+Maj+Entrée.

Donc on peut y remplacer SOMMEPROD par SOMME.

A+

 

[job75]

Et si l'on veut mettre le 1er terme en facteur on utilisera :

=SOMME((Janvier!$C6:$C200=B2)*(SIERREUR(--Janvier!$E6:$E200;0)+SIERREUR(-Janvier!$F6:$F200;0)))

 

[TooFatBoy]

Je pense qu'il y a pas mal de choses à réparer dans le classeur de #4.
Déjà, chez moi il y a une erreur à l'ouverture et Excel est obligé de réparer le fichier.

Ensuite, toutes les feuilles mensuelles vont jusqu'à la cellule AMJ1048576 !
Même la feuille "Récapitulatif et recherche" va jusqu'en AMJ16.

Il y a des plages nommées en erreur dans la liste des noms.

C'est également un grand bazar au niveau des MFC de chacune des feuilles.