Formule de calcul : somme en fonction d'un critère et sans doublon sur un 2e critère

[mvpolo]

Bonjour,

Je suis débutante en formule excel, et en général j'arrive à me débrouiller grâce aux ressources disponibles... mais là je bloque sur une formule.

J'ai un tableau non modifiable dans sa présentation avec par exemple :

Col. A > Col. B > Col. C > Col. D
108R > stylos > 2 > devis
108R > stylos > 2 > paiement
109R > stylos > 6 > règlement
109R > bacs > 3 > devis

Je veux compter automatiquement le nombre de stylos, et donc obtenir 8 en résultat en non 10 : autrement dis je veux faire une somme de la colonne C en fonction d'un critère donné en colonne B (jusque là je sais faire), mais sans doublon sur la colonne A (c'est là que je bloque).

Y aurait-il quelqu'un qui saurait faire ça ? (en espérant avoir été suffisamment claire)

merci d'avance

 

[Jocelyn]

Re : Formule de calcul : somme en fonction d'un critère et sans doublon sur un 2e cri

Bonjour le Forum,
Bonjour mvpolo,

Un essai en fichier joint

Jocelyn

 

[mvpolo]

Re : Formule de calcul : somme en fonction d'un critère et sans doublon sur un 2e cri

Bonjour Jocelyn et merci pour la réponse

J'ai téléchargé le fichier, et -si j'ai bien compris- la formule se base sur l'intitulé de la case au dessus... sauf que quand je met "stylos" à la place de "bacs" ça ne donne pas le bon résultat

J'ai également essayé d'appliquer ça sur mon tableau, sans résultat...

 

[Jocelyn]

Re : Formule de calcul : somme en fonction d'un critère et sans doublon sur un 2e cri

re,

si dans la cellule F2 de mon fichier tu mets stylos en F3 le résultat est 8 si en F2 Bacs resultat F3 =3

tu dis que cela ne fonctionne pas chez toi, mais que ce passe t'il quand tu change la valeur de F2 quel est le résultat en F3

Jocelyn

 

[job75]

Re : Formule de calcul : somme en fonction d'un critère et sans doublon sur un 2e cri

Bonjour mvpolo, Jocelyn,

Essayez cette formule matricielle :

=SOMME((NB.SI(DECALER(A1;;;LIGNE(A1:A1000));A1:A1000)=1)*(B1:B1000="stylos")*C1:C1000)

A valider par Ctrl+Maj+Entrée.

"stylos" peut être évidemment remplacé par une référence de cellule.

Edit : DECALER(A1;...) est suffisant, plutôt que DECALER(A1:A1000;...)

A+

 

[mvpolo]

Re : Formule de calcul : somme en fonction d'un critère et sans doublon sur un 2e cri

@ Jocelyn : en fait, quand je change l'intitulé en F2, ça ne change pas le résultat, à moins d'entrer dans la cellule et de revalider.

@ Job75 : bonjour, j'ai testé la formule, mais ça me met "#valeur" dès que je suis en situation de doublon

 

[job75]

Re : Formule de calcul : somme en fonction d'un critère et sans doublon sur un 2e cri

Re,

@ Job75 : bonjour, j'ai testé la formule, mais ça me met "#valeur" dès que je suis en situation de doublon

Je n'ai pas eu ce problème.

Pour valider une formule matricielle, cliquer dans la barre de formule puis appuyer simultanément sur les 3 touches CTrl Maj Entrée.

A+

 

[mvpolo]

Re : Formule de calcul : somme en fonction d'un critère et sans doublon sur un 2e cri

Je valide bien en formule matricielle, et ça marche si je n'ai pas de doublon en colonne A, mais ça affiche #valeur! si j'ai un doublon...

est-ce que ça peut venir de la version d'excel ? je travaille sous excel 2000

 

[Jocelyn]

Re : Formule de calcul : somme en fonction d'un critère et sans doublon sur un 2e cri

re,
Bonjour Job75,

hum hum je ne comprends pas pourquoi tu es amené a revalider ma formule pour quelle donne le bon résultat.

ne serais tu pas en calcul sur ordre pour le voir dans menu outils => option => onglet calcul et regarde si calcul automatique est bien coché

Jocelyn

 

[david84]

Re : Formule de calcul : somme en fonction d'un critère et sans doublon sur un 2e cri

Bonjour à tous,
une proposition suite au travail de Jocelyn te permettant de sélectionner l'item voulu par liste déroulante.
Cordialement

 

[mvpolo]

Re : Formule de calcul : somme en fonction d'un critère et sans doublon sur un 2e cri

Bonjour david84

Merci pour ta contribution, mais en fait la liste déroulante ne m'intéresse pas, le résultat se présentera sous forme de tableau récapitulatif du nombre de chaque objet... et mon excel doit avoir un soucis, car ça me fait le même problème qu'avec le fichier de jocelyn : en changeant la sélection dans la liste déroulante ça ne rafraichit pas le résultat (et la même formule dans mon tableau d'origine donne une case vide... ni erreur ni résultat )

Edit : @ jocelyn : bien vu, quelqu'un a dû toucher à mon poste de travail dans mon dos (y en a qui vont m'entendre) par contre je ne m'explique pas pourquoi la même formule dans mon tableau d'origine refuse d'afficher un résultat (case vide)

 

[Jocelyn]

Re : Formule de calcul : somme en fonction d'un critère et sans doublon sur un 2e cri

re,
Bonjour David84 ,

hum hum bon un premier point de résolu mais pour l'autre problème qui subsiste je ne vois vraiment pas, dans la mesure ou même si je test avec une valeur inexsistante table par exemple la cellule ne reste pas vide mais m'affiche 0

au pire j'arrive aprés test a lui faire affiché #N/A si il y a des vides intercalés dans la plage A (pour corriger cela si tu devais avoir des vides ajoutes dans la formule juste avant le ; qui précede la fonction EQUIV() cette partie *(A1:A6<>"") ) donc voila je ne sais pas trop quoi te dire

Jocelyn

 

[jeanpierre]

Re : Formule de calcul : somme en fonction d'un critère et sans doublon sur un 2e cri

Bonjou( mvpolo,, Jocelyn, Job, david84,

Pas d'affirmation hative, je cite : "quelqu'un a dû toucher à mon poste de travail dans mon dos (y en a qui vont m'entendre)"

Pas forcément, il suffit que tu aies ouvert un fichier en mode Calcul sur ordre pour modifier tous tes fichiers. Un peu pénible des fois, mais bon, c'est comme cela.

Bonne journée.

Jean-Pierre

 

[mvpolo]

Re : Formule de calcul : somme en fonction d'un critère et sans doublon sur un 2e cri

Bonjour jeanpierre

c'est bon à savoir que le paramétrage peut se modifier "spontanément"

Sinon pour revenir au problème, je veux faire mon tableau avec la formule sur un autre onglet que le tableau de données, est-ce que ça peut poser problème ?

Ou bien si dans ma colonne B ou C j'ai des cases vides ?

 

[david84]

Re : Formule de calcul : somme en fonction d'un critère et sans doublon sur un 2e cri

Re;
Bonjour Jocelyn,
Merci pour ton info. Je ne pense pas que cela provienne de ta formule.
Concernant la valeur inexistante qui t'affiche 0, j'ai regardé dans l'aide d'Excel concernant cette fonction, il est dit que :
-"Si l'argument tableau_données ne contient aucune valeur, la fonction FREQUENCE renvoie une matrice de zéros." (J'ai testé en supprimant toutes les valeurs de la colonne B et cela me renvoie 0)
- "La fonction FREQUENCE ne tient compte ni des cellules vides ni du texte."
Peut-être que l'affichage du 0 est donc normale...
Autre chose, peux-tu me dire à quoi sert le "equiv(....)*1?
Ta formule semble bien fonctionner sans...tout du moins dans cet exemple-ci !
Cordialement