XL 2016 Somme d'une colonne sur deux

[BBdoc31]

Bonjour à tous,

Voilà je suis entrain de faire un tableau et je galère à faire une formule avec la somme d'une colonne sur deux dans ce tableau.

j'ai dans un premier temps testé sur 3 itérations, donc assez simple mais je dois le faire sur 51 semaines, là ça commence à devenir ingérable vu la longueur de la formule.

=SOMME(SOMME.SI($A$4:$G$16;J5;$C$4:$C$16);SOMME.SI($A$4:$G$16;J5;$E$4:$E$16);SOMME.SI($A$4:$G$16;J5;$G$4:$G$16))

sur 51 semaines

=SOMME.SI($A$4:$CY$100;DA5;$C$4:$C$100)+SOMME.SI($A$4:$CY$100;DA5;$E$4:$E$100)+SOMME.SI($A$4:$CY$100;DA5;$G$4:$G$100)+SOMME.SI($A$4:$CY$100;DA5;$I$4:$I$100)+SOMME.SI($A$4:$CY$100;DA5;$K$4:$K$100)+SOMME.SI($A$4:$CY$100;DA5;$M$4:$M$100)+SOMME.SI($A$4:$CY$100;DA5;$O$4:$O$100)+SOMME.SI($A$4:$CY$100;DA5;$Q$4:$Q$100)+SOMME.SI($A$4:$CY$100;DA5;$S$4:$S$100)+SOMME.SI($A$4:$CY$100;DA5;$U$4:$U$100)+SOMME.SI($A$4:$CY$100;DA5;$W$4:$W$100)+SOMME.SI($A$4:$CY$100;DA5;$Y$4:$Y$100)+SOMME.SI($A$4:$CY$100;DA5;$AA$4:$AA$100)+SOMME.SI($A$4:$CY$100;DA5;$AC$4:$AC$100)+SOMME.SI($A$4:$CY$100;DA5;$AE$4:$AE$100)+SOMME.SI($A$4:$CY$100;DA5;$AG$4:$AG$100)+SOMME.SI($A$4:$CY$100;DA5;$AI$4:$AI$100)+SOMME.SI($A$4:$CY$100;DA5;$AK$4:$AK$100)+SOMME.SI($A$4:$CY$100;DA5;$AM$4:$AM$100)+SOMME.SI($A$4:$CY$100;DA5;$AO$4:$AO$100)+SOMME.SI($A$4:$CY$100;DA5;$AQ$4:$AQ$100)+SOMME.SI($A$4:$CY$100;DA5;$AS$4:$AS$100)+SOMME.SI($A$4:$CY$100;DA5;$AU$4:$AU$100)+SOMME.SI($A$4:$CY$100;DA5;$AW$4:$AW$100)+SOMME.SI($A$4:$CY$100;DA5;$AY$4:$AY$100)+SOMME.SI($A$4:$CY$100;DA5;$BA$4:$BA$100)+SOMME.SI($A$4:$CY$100;DA5;$BC$4:$BC$100)+SOMME.SI($A$4:$CY$100;DA5;$BE$4:$BE$100)+SOMME.SI($A$4:$CY$100;DA5;$BG$4:$BG$100)+SOMME.SI($A$4:$CY$100;DA5;$BI$4:$BI$100)+SOMME.SI($A$4:$CY$100;DA5;$BK$4:$BK$100)+SOMME.SI($A$4:$CY$100;DA5;$BM$4:$BM$100)+SOMME.SI($A$4:$CY$100;DA5;$BO$4:$BO$100)+SOMME.SI($A$4:$CY$100;DA5;$BQ$4:$BQ$100)+SOMME.SI($A$4:$CY$100;DA5;$BS$4:$BS$100)+SOMME.SI($A$4:$CY$100;DA5;$BU$4:$BU$100)+SOMME.SI($A$4:$CY$100;DA5;$BW$4:$BW$100)+SOMME.SI($A$4:$CY$100;DA5;$BY$4:$BY$100)+SOMME.SI($A$4:$CY$100;DA5;$CA$4:$CA$100)+SOMME.SI($A$4:$CY$100;DA5;$CC$4:$CC$100)+SOMME.SI($A$4:$CY$100;DA5;$CE$4:$CE$100)+SOMME.SI($A$4:$CY$100;DA5;$CG$4:$CG$100)+SOMME.SI($A$4:$CY$100;DA5;$CI$4:$CI$100)+SOMME.SI($A$4:$CY$100;DA5;$CK$4:$CK$100)+SOMME.SI($A$4:$CY$100;DA5;$CM$4:$CM$100)+SOMME.SI($A$4:$CY$100;DA5;$CO$4:$CO$100)+SOMME.SI($A$4:$CY$100;DA5;$CQ$4:$CQ$100)+SOMME.SI($A$4:$CY$100;DA5;$CS$4:$CS$100)+SOMME.SI($A$4:$CY$100;DA5;$CU$4:$CU$100)+SOMME.SI($A$4:$CY$100;DA5;$CW$4:$CW$100)+SOMME.SI($A$4:$CY$100;DA5;$CY$4:$CY$100)

Est-ce qu'il y a un moyen de faire plus simple ? car vu la longueur de la formule, une omission est vite arrivée.

 

[Victor21]

Bonjour.

Essayez : =SOMMEPROD(($B$4:$CY$100)*($A$4:$A$100=$DA5)*(MOD(COLONNE(B$4:CY$100);2)=1))

 

[JHA]

Bonjour à tous,

A essayer en "K5" de "Feuil1"

=SOMMEPROD(($A$4:$A$16=$J5)*(MOD(COLONNE($B$4:$G$16);2)=1);$B$4:$G$16)

Copier vers le bas
Edit: Bonjour Victor21
Pour l'onglet "MCB"

=SOMMEPROD(($A$4:$A$100=$DA5)*(MOD(COLONNE($B$4:$CY$100);2)=1);$B$4:$CY$100)

JHA

 

[Dugenou]

Bonjour à tous,
une autre possibilité : =SOMMEPROD(($A$4:$A$100=DA5)*ESTNUM($C$2:$CY$2)*$C$4:$CY$100)
Cordialement

 

[zebanx]

=SOMMEPROD(($A$4:$A$100=DA5)*ESTNUM($C$2:$CY$2)*$C$4:$CY$100)

Bonjour Dugenou, JHA, Victor21, BBDOC et le forum

Merci pour vos formules.

@Dugenou
Sauf erreur, comment faire sur votre formule svp au cas où les colonnes de "poste" ne contiennent pas des chiffres mais des lettres ?
Merci pour votre réponse, cdlt

 

[Dugenou]

Bonjour,
Ma formule utilise la ligne des N° de semaine, pas celle des N° de poste
Cordialement

 

[zebanx]

Bonjour,
la formule utilise la ligne des N° de semaine, pas celle des N° de poste
Cordialement

Il faut donc vraiment que ce soit des chiffres partout sans afficher d'erreur.
On ne peut avoir un step différent (cas de MOD()) en considérant que les colonnes non utilisées puissent contenir autre chose que des chiffres. Dans l'exemple, c'est effectivement le cas (colonne CF ou CD en modifiant sur la ligne 8 en texte / alphaN. par exemple fera apparaitre un #valeur sur le résultat de la formule ).
Dommage.

 

[Dugenou]

Re,
Je ne vous suis pas : les colonnes avec les postes ne sont pas utilisées : vous pouvez bien y mettre ce que vous voulez : on utilise les colonnes avec les N° de semaine (CE et CG par exemple) dans le calcul.
voir pj
Il faut que le seul chiffre dans la ligne des N° de semaine soit le N° de semaine et dans ce cas on peut ne pas avoir toujours le même intervalle. Si vous voulez travailler à partir de la ligne des postes : il faudra trouver un critère commun (genre : moins de x caractères)
Par contre avec les formules mod de Victor et JHA : il est impératif d'avoir toujours le même nombre de colonnes entre chaque mais il suffit de changer le 2 dans ces formules pour un step différent.
Cordialement

 

[zebanx]

Re-

Plutôt qu'un commentaire, un fichier est plus approprié
Je remplace en CB5 le chiffre par une lettre, et les erreurs (#valeur) en sommeprod s'affichent sur toutes les lignes. Je cherche juste à savoir comment contourner ce point là pour la formule, bien utile, que vous avez déjà proposée.

cdlt

 

[Dugenou]

Alors on peut passer par une "vraie" formule matricielle
=SOMME(SI(($A$4:$A$100=DA5)*($B$2:$CY$2<>"N° semaine");$B$4:$CY$100))
validée par ctrl+maj+enter, se recopie vers le bas

Voir pj

 

[zebanx]

Parfait! Je te remercie Dugenou et désolé de t'avoir fait perdre ton temps sur les posts #6 et #8.
Bon apm ++

 

[R@chid]

Bonjour @ tous,
à partir de la version Excel2007, en DB4 :

=SOMME(SIERREUR((MOD(COLONNE(B4:CY4);2)=1)*B4:CY4;))

toutes les versions, en DB4 :

=SOMME(SI((MOD(COLONNE(B4:CY4);2)=1)*ESTNUM(B4:CY4);B4:CY4))

les deux formules à valider par Ctrl+Shift+Enter et à tirer vers le bas


Cordialement

 

[R@chid]

J'ai pas pris en compte le "N° OF" pardon.

 

[BBdoc31]

Bon, j'ai apprécié vos réponses mais la chose est un peu plus compliquée.
Je dois faire une synthèse de plusieurs feuilles sur une seule et selon des critères définis.
Je n'arrive toujours pas à comprendre comment marche la fonction MOD.

Bon, je triche un peu sur mon fichier car je ne sais pas comment faire.
J'ai fait un récap sur les lignes 110 à 130 puis je vais chercher le résultat dans synthèse OF pour chaques semaines.

Je sais que l'explication n'est pas terrible mais en ouvrant le fichier, c'est un peu plus simple à comprendre.

 

[Dugenou]

La fonction MOD te renvoie le reste de la division (ici par 2):
Mod(1;2) = 1
Mod(2;2) = 0
Mod(3;2) = 1 etc...