Somme de cellules en fonction de la valeur d'une cellule

[crochpat]

Bonjour à tous,

Difficile de décrire dans l'intitulé de la discussion ce que je souhaite obtenir.

Voici mon problème :
J'aimerai automatiser le calcul dans des tableaux comportant des centaines de lignes (voir exemple joint).

Voici ce que je dois obtenir :

1. Je cherche à faire la somme des nombres en jaune sur un an (par ligne) : c'est ce qui est en couleur verte (colonne A).
2. La première difficulté consiste à trouver dans les colonnes, à partir de la colonne F, la [Date debut] qui correspond à 1 an environ avant [Date Fin] (colonne E). Ce qui rajoute à la difficulté, c'est que les dates ne sont pas souvent espacées de 365 jours pile.
3. Ensuite, je dois faire la somme de toutes les cellules de chaque ligne (je les ai mises en jaune pour vous permettre de bien les identifier), de la première (colonne C) à la colonne située juste avant la date trouvée en 2.

A votre avis, existe t-il une solution à mon problème ?

D'avance merci beaucoup !

 

[mapomme]

Bonjour @crochpat,

Un essai pour ce qu'en ai compris. La formule en A2 est matricielle et doit être copier/tirer vers le bas.

Formule matricielle en A2:

=SOMME(SIERREUR(($D$1:$BW$1="Date Fin")*(TEXTE(D2:BW2;"aaaamm") >TEXTE(DATE(ANNEE(E2);MOIS(E2)-12;1);"aaamm"))*DECALER(D2:BW2;0;-2);0))

Formule Matricielle:

• à valider par la combinaison des trois touches Ctrl+Maj+Entrée au lieu de la seule touche Entrée
• ne pas entourer la formule d'accolade au clavier. C'est la validation par Ctrl+Maj+Entrée qui ajoute les accolades
• une fois saisie et validée, une formule matricielle peut être copiée et collée
• à chaque édition/modification de la formule matricielle, penser à la valider par Ctrl+Maj+Entrée

 

[crochpat]

Mapomme, tu es un génie !

C'est exactement cela.

Je serai incapable de recréer cette formule 🙂, mais l'important, c'est que ça marche.

Merci beaucoup !

 

[zebanx]

Bonjour @mapomme, crochpat, le forum

Une autre méthode (par VBA) qui colorie toutes les valeurs de référence si vous souhaitez conserver un visuel.
J'ai un delta en cellule B7 (rajout d'une condition en Mise En Forme Conditionnelle).
Et bravo à Mapomme pour cette formule.

xl-ment
zebanx

 

[crochpat]

Merci également Zebanx,

J'ai une dernière question pour terminer (voir fichier joint).

Dans le tableau que j'obtiens, j'ai parfois des lignes avec la même référence principale, mais avec 2 à 6 lignes de valeur. Dans l'exemple ici, HC, HP et HD (colonne D).
Je dois trier ces valeurs pour les mettre dans un tableau (il faut que je les transpose dans le bon ordre) dans un autre document excel.

Pouvez-vous m'aider ?

D'avance merci.

 

[zebanx]

Re-

Un somme.si ne ferait-il pas l'affaire ?

 

[crochpat]

Non, cela ne marche pas comme ça.
Une fois que les calculs de ma première question sont faits, il faut encore que j'injecte les résultats dans un autre tableau dont je remplis chaque ligne.
Chaque ligne correspond à un client. Je ne peux donc pas faire la somme de HC pour tous les clients par exemple.
Ce que je cherche à faire, c'est classer les résultats obtenus en faisant une ligne par client pour que mon tableau se remplisse tout seul en fonction d'un couple [code client/HP], [code client/HC] et [code client/HD].

 

[JHA]

Bonjour à tous,

Avec ce que je comprends, deux solutions sur ton fichier.

JHA

 

[crochpat]

Bonjour et merci pour vos propositions,

Je m'aperçois que mon exemple n'est pas clair.
J'ai donc rajouté dans mon exemple des lignes pour le rendre plus compréhensible (voir document joint).
Ce que je cherche à faire, c'est construire un nouveau tableau à partir des résultats du premier, sachant que j'ai des valeurs en colonne A qui ont parfois plusieurs options.

Pour y arriver, j'utilise la fonction RECHERCHEV comme vous le verrez dans mon exemple.
Mais ma méthode m'impose de créer une colonne artificielle (colonne B) que je voudrais éviter de créer pour rien (si cela est possible).

 

[JHA]

Bonjour à tous,

A essayer

JHA

 

[mapomme]

Bonjour à toutes et tous,

Pour le fun, une autre formule matricielle en B11 à tirer/copier vers la droite et le bas :

=SIERREUR(INDEX($F$2:$F$7;EQUIV(1;($A$2:$A$7=$A11)*($C$2:$C$7=B$10);0));"")