XL 2016 formule SOMMEPROD sur deux onglets

[casmaran]

Bonjour à tous,
j'ai une formule sur une page qui me permet de compter des heures de vols sur douze mois en arrière à partir de 'AUJOURDHUI' :

=SOMMEPROD((M6:M38)*(A6:A38>=MOIS.DECALER(AUJOURDHUI();-12)))

cette formule fonctionne bien mais en ajoutant un onglet je voudrais que le calcul se fasse sur les deux onglets, et là je sèche pour trouver la formule malgré une recherche sur le forum.


Je joins un extrait de mon fichier, la formule est en cellule H42.
Merci par avance de votre aide.

 

[Wayki]

Salut,
Je connaissais pas ta formule et j'étais sceptique mais ça fonctionne, cool aha
T'as juste à faire une somme de tes 2 sommeprod :

{=SOMMEPROD(Page32!L6:M38)*(A6:A38>=MOIS.DECALER(AUJOURDHUI();-12))+SOMMEPROD(Page33!L6:M38)*(A6:A38>=MOIS.DECALER(AUJOURDHUI();-12))}

A +

 

[casmaran]

Salut,
Je connaissais pas ta formule et j'étais sceptique mais ça fonctionne, cool aha
T'as juste à faire une somme de tes 2 sommeprod :

A +

Bonjour Wayki,
merci de ta réponse
j'avais essayé de copier deux fois la formule, mais en fait ça cumule la totalité de la page 32 + la 33 (31 h 19) ce qui n'est pas bon, la formule devrait s'arrêter au 15/08/2020 (cellule A22 - P32) ce qui ferait les douze mois depuis le dernier vol le 06/08/2021 soit normalement 17 h 09 en comptant manuellement.

 

[Wayki]

Un essai ci-joint.
A +

 

[casmaran]

super formule qui prend les deux pages ! cependant j'ai un petit décalage de 16 minutes par rapport au comptage manuel, j'ai essayé la formule avec -30 jours au lieu de 365, le résultat donne 3h16 alors qu'il devrait donné 3h00 (1h30 de la page 33 + les deux derniers vols de la page 32, 0h51 +0h39 ce qui ferait 3h00), il y a donc toujours ce décalage de 0h16 mn, je ne comprend pas d'où ça peut venir.
je me demande si SOMMEPROD est bien adapté pour plusieurs feuilles, j'ai vu sur des forums que SOMMEPROD "n'aimait pas les calculs sur plusieurs feuilles".
Merci en tout cas pour ta formule.

 

[Wayki]

Si il fait constamment un décalage de 16min, essaie tout simplement de les enlever dans la formule ?

 

[vgendron]

Si il fait constamment un décalage de 16min, essaie tout simplement de les enlever dans la formule ?

Hello le fil
cette méthode me parait totalement approximative...
tu proposes d'enlever 16mn sans chercher à comprendre d'où ils viennent??
vous vous exposez à de nombreux déboires par la suite.... et il va devoir modifier la formule à chaque cellule parce que ici, c'est 16mn, mais la cellule d'a coté c'est 18mn... pas sérieux...

 

[vgendron]

@casmaran
dans ton premier post, tu parles d'une formule en H42 = SOMMEPROD((M6:M38)*(A6:A38>=MOIS.DECALER(AUJOURDHUI();-12)))

dans ton fichier, ce n'est pas ce que je vois dans la feulle 33
tu as une sommeprod en matriciel
=SOMMEPROD(Page32!L6:M38)*(A6:A38>=MOIS.DECALER(AUJOURDHUI();-12))
résultat = 29:49

et dans la feuille 32, ce n'est pas la meme formule
=SOMMEPROD((M6:M38)*(A6:A38>=MOIS.DECALER(AUJOURDHUI();-12)))
résultat 15:39

H42 et h43 on la meme formule==> normal??


1) SommeProd est déjà matricielle==> pas besoin de valider avec Ctrl+Shift+Entrée
2) H42 de la feuille 32==> ca me semble cohérent et OK

3) qu'es tu censé calculer en H42 de la feuille 33 ??
le meme calcul sur la feuille 33 + le résultat de la feuille 32 ?
=SOMMEPROD((M6:M38)*(A6:A38>=MOIS.DECALER(AUJOURDHUI();-12)))+Page32!H42

à mon avis.. il manque quelque chose

 

[vgendron]

pour finir
les formules "3D" telles que
=somme(Page32age33!L41) ne fonctionnent pas avec des sommeprod.. du moins. je n'ai jamais vu quelque chose fonctionner
ca semble limité à des fonctions simples (Somme - Min - Max...)

 

[Wayki]

Hello le fil
cette méthode me parait totalement approximative...
tu proposes d'enlever 16mn sans chercher à comprendre d'où ils viennent??
vous vous exposez à de nombreux déboires par la suite.... et il va devoir modifier la formule à chaque cellule parce que ici, c'est 16mn, mais la cellule d'a coté c'est 18mn... pas sérieux...

C'est complètement approximatif oui je le cache pas, mais c'est également une solution parmis tant d'autres. Si ça fait le travail qu'il demande même par la suite, où est le soucis ?
Mais il est clair qu'il vaudrait mieux plancher sur le problème...

 

[casmaran]

@casmaran
dans ton premier post, tu parles d'une formule en H42 = SOMMEPROD((M6:M38)*(A6:A38>=MOIS.DECALER(AUJOURDHUI();-12)))

dans ton fichier, ce n'est pas ce que je vois dans la feulle 33
tu as une sommeprod en matriciel
=SOMMEPROD(Page32!L6:M38)*(A6:A38>=MOIS.DECALER(AUJOURDHUI();-12))
résultat = 29:49

et dans la feuille 32, ce n'est pas la meme formule
=SOMMEPROD((M6:M38)*(A6:A38>=MOIS.DECALER(AUJOURDHUI();-12)))
résultat 15:39

H42 et h43 on la meme formule==> normal??


1) SommeProd est déjà matricielle==> pas besoin de valider avec Ctrl+Shift+Entrée
2) H42 de la feuille 32==> ca me semble cohérent et OK

3) qu'es tu censé calculer en H42 de la feuille 33 ??
le meme calcul sur la feuille 33 + le résultat de la feuille 32 ?
=SOMMEPROD((M6:M38)*(A6:A38>=MOIS.DECALER(AUJOURDHUI();-12)))+Page32!H42

à mon avis.. il manque quelque chose

Bonjour vgendron,
pour récapituler :
- je veux calculer la somme des heures de vols effectuées sur mes deux pages P32 et P33 sur les douze mois passés,
- la 1ère formule que tu cite est celle que j'avais mis à l'origine uniquement sur la page 32 qui fonctionne bien mais uniquement sur une page,
- dans ton 2ème paragraphe 29:49 est le résultat que m'avait proposé Wayki en mettant + et en recopiant la saisie mais ça ne marche pas car la formule prenait la page entière 32. Wayki m'a alors proposé cette formule appliquée sur les deux pages "=SOMMEPROD((Page32!B6:B38>=TEXTE(AUJOURDHUI()-365;"aaaamm"))*Page32!M6:N38)+SOMMEPROD((Page33!B6:B38>=TEXTE(AUJOURDHUI()-365;"aaaamm"))*Page32!M6:N38), qui fonctionne mais avec ce décalage de 16 minutes supplémentaires,
- H42 comptabilise les colonnes L et M de mon ancien fichier (heures en double commande et heures en Cdt de bord, H43 ne prend que les heures Cdt de bord,
- donc j'en suis là avec la dernière formule ci-dessus en insérant une colonne suppl. en B pour la saisie aaaamm,
- j'ai également testé la formule en prenant une durée de 30 jours au lieu de 365, ça fonctionne mais toujours avec l'écart de 16 mn.
Je prends note pour ne pas valider avec Ctrl+Shift+Entrée.
merci de vous intéresser à mon cas.

 

[casmaran]

Bonjour vgendron,
pour récapituler :
- je veux calculer la somme des heures de vols effectuées sur mes deux pages P32 et P33 sur les douze mois passés,
- la 1ère formule que tu cite est celle que j'avais mis à l'origine uniquement sur la page 32 qui fonctionne bien mais uniquement sur une page,
- dans ton 2ème paragraphe 29:49 est le résultat que m'avait proposé Wayki en mettant + et en recopiant la saisie mais ça ne marche pas car la formule prenait la page entière 32. Wayki m'a alors proposé cette formule appliquée sur les deux pages "=SOMMEPROD((Page32!B6:B38>=TEXTE(AUJOURDHUI()-365;"aaaamm"))*Page32!M6:N38)+SOMMEPROD((Page33!B6:B38>=TEXTE(AUJOURDHUI()-365;"aaaamm"))*Page32!M6:N38), qui fonctionne mais avec ce décalage de 16 minutes supplémentaires,
- H42 comptabilise les colonnes L et M de mon ancien fichier (heures en double commande et heures en Cdt de bord, H43 ne prend que les heures Cdt de bord,
- donc j'en suis là avec la dernière formule ci-dessus en insérant une colonne suppl. en B pour la saisie aaaamm,
- j'ai également testé la formule en prenant une durée de 30 jours au lieu de 365, ça fonctionne mais toujours avec l'écart de 16 mn.
Je prends note pour ne pas valider avec Ctrl+Shift+Entrée.
merci de vous intéresser à mon cas.

j'avais oublié de joindre l'extrait de mon fichier actuel

 

[vgendron]

Une question:
page 32, page 33.. comment tu passes d'une page à l'autre??
lorsque la page 32 est complète (ligne38 remplie) alors tu ajoutes une page..
et donc. dans la dernière page, tu souhaites le cumul des heures volées ?

je vois que la formule fait référence à un autre classeur.. (que nous n'avons pas. donc. pb de reférence dans la formule)
c'est quoi cet autre classeur??

 

[vgendron]

comme il y a beaucoup de formules dans tous les sens dans ton nouveau fichier,
je te propose celui ci en retour: Voir la feuille RECAP

en B2: formule qui te donne les heures volées sur CESNA-F152 DANS la page 32
en F2: meme formule pour la page 33

en C1 et D1: te donne la période complète saisie dans la page 32
et G1 et H2: période complète saisie dans la page 33

en B5: formule qui te donne Aujourd'hui MOINS 12 mois
en C5: aujourd'hui
en B6: formule qui te donne les heures volées (colonne N) pour la période B5 - C5 DANS la page 32
en F6: comme B6 pour la page 35

en B8 et C8: des dates pour choisir la période que tu veux
en B9 et F9: les formules pour compter les heures volées pendant la périofe

colonne J: c'est juste le total des pages 32 et 33

 

[casmaran]

pour la saisie des heures, le classeur est composé de 33 pages identiques quand la dernière est remplie (lignes 38) j'ajoute une page, ce classeur, en sus des pages 1/38, comporte d'autres pages nommées Accueil-Stat avions-Ventilation des heures-Données graphiques-Activité aérienne.

Je souhaite le cumul des heures sur douze mois en arrière à partir de la dernière ligne de la dernière page en l'occurence ligne 7 de la page 33.
L'autre classeur est une copie de ce même classeur sans les pages 1 à 38 de saisie des heures.

Je n'arrive pas à ouvrir le fichier Pages 32 33 bis que tu m'as joint en retour